Search code examples
excelsumifs

Excel: How do I reference a date when using SUM IF for multiple columns and date rows

I've tweak this a few times. It seems to be my issue is in the IF Function when I reference a date it does not pick it up. Do I need to use a specific function for calling a date? If I change my date to something random, like DAN, the formula works fine.

Example Data table:

Data Sheet

What I want it summarized as:

Summary Sheet

Here's what formula i'm trying to use: =SUM(IF(C2:H2="California",IF(B3:B14="01/01/2018",C3:H14)))

Solution

  • Your formula actually works just fine, provided you enter it as array formula (with Ctrl+Shift+Enter):

    enter image description here

    I modified it slightly only so it doesn't take 'hard coded' values and with some careful range locking, this is the final one:

    =SUM(IF($C$2:$H$2=C$16,IF($B$3:$B$14=$B17,$C3:$H3)))

    Then again, I'd usually go for SUMPRODUCT which feels more natural to me now:

    =SUMPRODUCT($C$3:$H$14*($B$3:$B$14=$B17)*($C$2:$H$2=C$16))
            <--  1 --> <------ 2 ------> <-----  3 ----->

    Where 1 is the full range, 2 is the first condition and 3 is the second condition.