# SUMIFS Formula with Dates Not Working

1. ## SUMIFS Formula with Dates Not Working

I'm trying to sum gross amounts (L13:L22) based on 2 criteria -- the first is if the amount is "line haul" (A13:A22) and second is if the date matches a certain MONTH (R13:R22 matches to S13). This is my formula, but it's producing a 0 result.

=SUMIFS(L13:L22,A13:A22,"Line Haul",R13:R22,"=S13")

In the attached spreadsheet the formulas are in U13 and U14 (on the 01-03-2020 & 01-10-2020 tabs)

I'm thinking the problem may lie with my date references (in columns S & R) ... but I'm stumped.

What am I doing wrong??

2. ## Re: SUMIFS Formula with Dates Not Working

Try this:

=SUMIFS(L13:L22,A13:A22,"Line Haul",R13:R22,S13)

3. ## Re: SUMIFS Formula with Dates Not Working

I had tried that one too, but it also produces a 0 result I just don't get it! lol

4. ## Re: SUMIFS Formula with Dates Not Working

OK - so attach a sample workbook, please.

5. ## Re: SUMIFS Formula with Dates Not Working

Hmmm, I thought I had a attached a workbook .. are you not able to see it?

6. ## Re: SUMIFS Formula with Dates Not Working

Oops - LOL!!! I'll have a look.

7. ## Re: SUMIFS Formula with Dates Not Working

Phew .. was getting worried there!! Thank you!!!

8. ## Re: SUMIFS Formula with Dates Not Working

The values in column R are all 12 - meaning December. Why do you think that any of these match S13?

9. ## Re: SUMIFS Formula with Dates Not Working

I thought it was just formatting ... Column R is General and Column S is mmm. Even if I change the formatting to match, it's not working ... AND the 12's change to Jan when the formatting is mmm. It's baffling!

10. ## Re: SUMIFS Formula with Dates Not Working

I am seeing a mixture of US and UK style formatting for the dates.

Let's go back to basics: explain in words what you want that cell to sum and why.

11. ## Re: SUMIFS Formula with Dates Not Working

I will be adding tabs to the workbook for each week. Within each week, I need to sum all the Gross Amounts for all "line hauls" that were delivered in the current month (U14) and the previous month (U13), based on the Settlement Date (H3) - which is a formula that matches the tab name. I will then be adding formulas in my YTD Summary to reference total line hauls for each month (this formula is not yet created).

12. ## Re: SUMIFS Formula with Dates Not Working

01-03-2020

U13=IF(\$S13<>"",SUMPRODUCT((\$A\$13:\$A\$22=\$A\$13)*(\$D\$13:\$D\$22<>"")*(MONTH(\$D\$13:\$D\$22)=MONTH(S13))*(\$L\$13:\$L\$22)),"")

copy down

01-10-2020

U13=IF(\$S13<>"",SUMPRODUCT((\$A\$13:\$A\$22=\$A\$13)*(\$D\$13:\$D\$22<>"")*(MONTH(\$D\$13:\$D\$22)=MONTH(\$S13))*(\$L\$13:\$L\$22)),"")

Copy down

13. ## Re: SUMIFS Formula with Dates Not Working

OMG!!! THANK YOU, THANK YOU CARACALLA ---- that worked perfectly!!!!!! Wooohooooooo!!!

14. ## Re: SUMIFS Formula with Dates Not Working

You are welcome

15. ## Re: SUMIFS Formula with Dates Not Working

Since "Line Haul" may not always appear on the first row, I had to change your formula to the following ...

=IF(\$S13<>"",SUMPRODUCT((\$A\$13:\$A\$22="Line Haul")*(\$D\$13:\$D\$22<>"")*(MONTH(\$D\$13:\$D\$22)=MONTH(S13))*(\$L\$13:\$L\$22)),"")

Even with this change, it works beautifully! Thanks again!!!

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