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?

Accepted Answer

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:

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

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

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.)

SUMIF with month criterion

Solution

Problem