Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SUMIF with month criterion

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?

like image 834
Finger twist Avatar asked Oct 19 '25 05:10

Finger twist


1 Answers

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

like image 194
richardtallent Avatar answered Oct 22 '25 00:10

richardtallent



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!