Thread: Using SUMIF

I am trying to sum the values of entries corresponding to a particular month in a summary table. I have tried using the following formula:

=SUM(IF(MONTH('Daily KPIs'!$B$1:$B$249)=MONTH('Monthly KPIs vs Plan'!O2),$B$1:$B$249,0))

Does anyone know a better way of doing it? Thank you for your time.

A SUMPRODUCT formula will likely work, but it will be easier to provide and exact answer f you upload a sample workbook and manually show the results you want.

Please complete your profile to, at least, show which version of Excel you are using. Version 2007 has a new feature called SUMIFS that could prove useful.

I'm not a fan of array formulas since they can be a resource hog, especially if used much.

Here is an alternative formula using SUMPRODUCT.
=SUMPRODUCT((MONTH(Data!$A$3:$A$42)=MONTH(C$1))*(Data!$B$3:$B$42))

SUMPRODUCT is not really any more efficient than an Array - it's just slightly more robust in so far as does not require CSE -- it's processed in similar vein.

msbaath, although less elegant it's nearly always better in these scenarios (in efficiency terms) to either

a) combine 2 SUMIF functions

b) use helper @ source

Example of

a)
'Monthly Summary'!C2:
=SUMIF(Data!$A:$A,">="&C$1,INDEX(Data!$B:$D,0,ROWS(B$2:B2)))-SUMIF(Data!$A:$A,">="&DATE(YEAR(C$1),MONTH(C$1)+1,1),INDEX(Data!$B:$D,0,ROWS(B$2:B2)))
copied across matrix

Example of
b)
Data!E3: =A3-DAY(A3)+1
copied down

this normalises the dates such that all appear as 1st of month meaning 1 SUMIF required

'Monthly Summary'!C2
=SUMIF(Data!$E:$E,C$1,INDEX(Data!$B:$D,0,ROWS(B$2:B2)))
copied across matrix