# Using Formula MONTH() in SUMIFS()

Copper Contributor

# Using Formula MONTH() in SUMIFS()

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?

7 Replies

# Re: Using Formula MONTH() in SUMIFS()

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

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

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

# Re: Using Formula MONTH() in SUMIFS()

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!

# Re: Using Formula MONTH() in SUMIFS()

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

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

# Re: Using Formula MONTH() in SUMIFS()

Spoiler

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

# Re: Using Formula MONTH() in SUMIFS()

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

# Re: Using Formula MONTH() in SUMIFS()

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