SUMIF with month criterion

Question

I have:

=SUMIF('Log'!A2:A139,MONTH('Log'!A2:A139)=1,'Log'!M2:M139)

Colums A in the 'Log' sheet contains dates. I want only to sum the values in the M column of the Log sheet IF the month is January.

The current expression keeps on returning 0, am I doing anything wrong?

Answer 1

In the second argument, the criteria has to relate back to each cell in the first argument. So, giving MONTH() a range isn't going to do any good there, it just keeps comparing A x to MONTH(A2) and getting a FALSE.

There are two easy solutions:

1. Create a scratch column, say N, with MONTH(A2), then use that column:

   =SUMIF('Log'!N2:N139,1,'Log'!M2:M139)
   

2. Use an Array formula:

   {=SUM('Log'!M2:M139 * IF(MONTH('Log'!A2:A139)=1,1,0))}
   

(Array formulas aren't entered with literal {} around them, they are entered using Shift-Enter and just appear with the {} around them in Excel.)