Forum Discussion
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
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")
- 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*")
and
=SUMPRODUCT(C2:C1000, (MONTH(A2:A1000)=8)*ISNUMBER(SEARCH("Groceries", B2:B1000)))
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.
