Using Formula MONTH() in SUMIFS()

Copper Contributor

My simplified table looks like this - 


I am trying to calculate the sum of amount if 1. the month of the date is August (8); 2. the category is "Groceries". 

The formula I used was =SUMIFS(C:C, MONTH(A:A), 8, B:B, "Groceries"), but it always returns error. 

If I change MONTH(A:A), 8 to A:A, "8/1/2023", it works, but it doesn't work if I want to verify the month. 

What is the problem with this formula? 

7 Replies


MONTH(A:A) is not a range but an array.

The criteria_range arguments of SUMIFS must be ranges, not arrays.

You can use SUMPRODUCT instead:


=SUMPRODUCT(C2:C1000, (MONTH(A2:A1000)=8)*(B2:B1000="Groceries"))


(I'd use finite ranges instead of entire columns for reasons of performance.)


Alternatively, create a helper column. For example enter the following formula in D2:


and fill down. You can then use

=SUMIFS(C:C, D:D, 8, B:B, "Groceries")

@Hans Vogelaar 

I tried SUMPRODUCT but it sometimes works, sometimes gives error and sometimes gives 0. I am really confused, so I simplified my table further to check for the issue. Could you help me to check why the formula is returning 0 here? Thank you! 




SUMPRODUCT() ignores logical values. SUMPRODUCT(B2:B4, --(A2:A4="Groceries"))


And since you omitted the MONTH() criteria you can use SUMIF() again.



@Detlef Lewin 


I am trying to do something similar, I want to look at sales per sales rep per month where on another tab I have a total revenue figure and a criteria of closed one next to it


A1 is the actual revenue

A2 is the target

A3-4 is the month range


I started with this but cannot build it out without errors =SUMIF(Opportunities!O:O, "Closed Won", Opportunities!N:N)




what if there were various words in the cells under the Category heading in each cell instead of just "Groceries"? Can you still isolate Groceries? If so, how? Ive seen "*...*" used in sumif and sumifs formulas


Using the example from higher up:


=SUMIFS(C2:C1000, B2:B1000, "*Groceries*")




=SUMPRODUCT(C2:C1000, (MONTH(A2:A1000)=8)*ISNUMBER(SEARCH("Groceries", B2:B1000)))

Thank you