Need SUMIFS to return multiple rows
The error I'm getting:
Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 3. Actual: 1.
I think I know why I'm getting this error. As you can see in the formula and table below, there are three months worth of data. This would cause three rows with the first Array. However, the SUMIFS function is only returning one number it seems like. However, I need it to return a row for each month. I suspect 3 rows are expected in the error because there are three months, as returned by the first array. Is my understanding of this error correct?
Formula:
=ArrayFormula({{unique(text(A1:A,"MMM YY"))},
{ABS(SUMIFS(C1:C,B1:B,G1,text(A1:A,"MMM YY"), unique(text(A1:A,"MMM YY"))))}})
Note: I am using entire columns so the function will still work without adjustment when more data is added.
Data:
| Date | Category | Payment |
|---|---|---|
| 01/03/2021 | Food | $5 |
| 02/03/2021 | Food | $5 |
| 02/06/2021 | Fee | $5 |
| 03/03/2021 | Food | $5 |
G1 is Food
Expected output:
| Jan 21 | $5 |
| Feb 21 | $5 |
| Mar 21 | $5 |
How can I get SUMIFS to return a new row for each month? Do I need to use a function like OFFSET?
You can use SUMIF + ARRAYFORMULA instead of SUMIFS
=ArrayFormula(unique(if(A2:A<>"",{text(A2:A,"MMM YY"),sumif(text(A2:A,"MMM YY")&B2:B,text(A2:A,"MMM YY")&"Food",C2:C)},)))
