# SUMIFS formula not working with Dates

1. ## SUMIFS formula not working with Dates

I think I can use the SUMIFS formulato add qty's based on whether the transaction date falls within a specified date range.

I have a workbook with 2 sheets; 1st sheet has the qty's and the transaction dates and the 2nd sheet has the criteria.

Qty's in Sheet 1 are in Column H
Dates in Sheet 1 are in Column D

So in sheet 2 i need a formula in each row to sum the qty's in sheet 1 column H as long as the date in sheet 1 column D falls within the dates in sheet 2 column C and sheet 2 column U.

My formula reads: =SUMIFS(Sheet1!H:H, Sheet1!D:D, ">="&Sheet2!C2, Sheet1!D:D,"<="&Sheet2!U2)

From there I would expect to be able to copy the formula down so that this calculation is performed for each row in sheet 2 (several date ranges). Any ideas why this isn't working?

2. ## Re: SUMIFS formula not working with Dates

Your formula looks OK to me, what result do you get, an error or the wrong value?

3. ## Re: SUMIFS formula not working with Dates

I just get zeros. I've gone through the data and have confirmed there should be qty's summed as there are transaction dates that do fall between the date range on sheet 2

4. ## Re: SUMIFS formula not working with Dates

Either some or all of your dates are not really dates, but text strings that look like dates.
Or the values you are summing aren't really numbers, but "numbers stored as text"

Can you post a sample book?

5. ## Re: SUMIFS formula not working with Dates

Make sure that your quantity column contains numbers, not text formatted numbers - what do you get with

=COUNT(Sheet1!H:H)

That should count the numbers in that column - if you get zero with that formula then it's text formatted - try the same with the date columns - they should be numbers too

6. ## Re: SUMIFS formula not working with Dates

yeah it looks to be a problem with the date formatting in Sheet 1 column D.

Thanks to all for your help

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1