SUMIFS or SUMPRODUCT with Multiple Columns and with Date Criteria

**eac8423** · 04-04-2014, 04:56 PM

I am trying to sum a range that is multiple columns (P6:T44) when three criteria are met. The first criteria is text (B6:B44) and the other two are dates (E6:E44 and F6:F44). T47 is the date I am comparing against. There are blank rows throughout.

First I did this: =SUMIFS(T6:T44,B6:B44,"Revolver",E6:E44,"<=" & T47,F6:F44,">=" & T47) which returned a VALUE error. Some Googling told me that SUMIFS doesn't work over multiple columns. Ok.

So, second, I did this: =SUMPRODUCT((B6:B44="Revolver")*(E6:E44<=T47)*(F6:F44>=T47),(P6:T44)) and I am still getting a VALUE error.

In both cases it works if I narrow the sum range to T6:T44, but I don't love the idea of doing this 5 times (one per column) and then summing those, especially since I will have to add a new column every month. Does anyone have an alternate solution? Thanks!

**TMS** · 04-04-2014, 05:07 PM

Try:

Formula:

Please Login or Register  to view this content.

Regards, TMS

**eac8423** · 04-04-2014, 05:12 PM

That did it! Thanks so much.

**TMS** · 04-04-2014, 05:20 PM

You're welcome. Thanks for the rep.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.