Using Formula MONTH() in SUMIFS()

Copper Contributor

My simplified table looks like this - 

DateCategoryAmount
8/1/2023Housing$100.00
8/1/2023Groceries$200.00
9/1/2023Groceries$100.00
9/1/2023Utilites$200.00

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

@Lkiia 

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:

=MONTH(A2)

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! 

Lkiia_0-1692588195553.pngLkiia_1-1692588209353.png

 

@Lkiia 

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

 

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

 

Spoiler
 

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

 

JANUARYFEBRUARYMARCHAPRIL
£50,000£32,000£30,000£21,000
£66,667£66,667£66,667£66,667
01/01/202401/02/202401/03/202401/04/2024
31/01/202429/02/202431/03/202430/04/2024

 

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

@Jlkirk 

Using the example from higher up:

 

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

 

and

 

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

Thank you