Search code examples
excelif-statementsumifs

Sumifs function with a criteria as a date to be equal with another date

I want to have in sumifs function, a criteria as a date to be equal with another date. I’ve tried:

=SUMIFS($N11:$AE11,$N11:$AE11,"<>0",$N9:$AE9,"<"&DATE(YEAR(FX11),MONTH(FX11),DAY(FX11)),$N9:$AE9,">"&DATE(YEAR(FX11),MONTH(FX11),DAY(FX11)))

But it seems it doesn’t work. Thanks.

Solution

  • If FX11 contains a real date, and you want your criteria to be equal to it, then try:

    =SUMIF($N$9:$AE$9,FX11,$N$11:$AE$11)

    If FX11 contains a date/time, then try:

    =SUMIF($N$9:$AE$9,INT(FX11),$N$11:$AE$11)

    Excel stores date/time as number of days and fractions of a day with 1 Jan 1900 = 1 (usually); so the INT function will remove the time portion.

    And, in doing a SUM, there is no need to test that an entry is non-zero since x + 0 = x