Using Formula MONTH() in SUMIFS()

[Lkiia]

My simplified table looks like this - 

Date	Category	Amount
8/1/2023	Housing	$100.00
8/1/2023	Groceries	$200.00
9/1/2023	Groceries	$100.00
9/1/2023	Utilites	$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? 

[#2]

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

[#3]

@HansVogelaar 

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! 

[#4]

@Lkiia 

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

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

[#5]

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)

JANUARY	FEBRUARY	MARCH	APRIL
£50,000	£32,000	£30,000	£21,000
£66,667	£66,667	£66,667	£66,667
01/01/2024	01/02/2024	01/03/2024	01/04/2024
31/01/2024	29/02/2024	31/03/2024	30/04/2024

[#6]

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

[#7]

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

[#8]

Thank you