Google sheet SUMIFs error "Array arguments to SUMIFS are of different size." even though they are fixed size

Question

This is my formula: =SUMIFS(Transactions!$B2:$B1000, "=2020_10", Transactions!$H2:$H1000, "=Salary", Transactions!$E2:$E1000)

So I'm trying to say "if the row has a date (string) of 2020_10 in col B, and a value of "Salary" in col H, add up col E (which contains a monetary amount)"

As you can see I've tried specifying the exact number of rows to try and ensure the "array arguments" are of the same size but it still throw the same error.

Transactions sheet:

• Formula for col B (date as a string): =IF(ISBLANK(A1000), "None", CONCATENATE(YEAR(A1000), "_", MONTH(A1000)))
• Formula for col H (Calculated Purchase Type): =IF(ISBLANK(G1000), "None", G1000)
• Col E: manually entered number

Screenshot of transactions sheet:

So I'd like row 9 counted in the SUMIFS, but not the other rows (as their Calculated Purchase Type (col H) isn't "Salary").

Answer 1

You seem to have the arguments in the wrong order.

The syntax of the function is:

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

So it's trying to sum the contents of column B, and using "=2020_10" and "=Salary" as criteria ranges.

Try

=SUMIFS(Transactions!$E2:$E1000, Transactions!$B2:$B1000, "=2020_10", Transactions!$H2:$H1000, "=Salary")

Answer 2

I had the arguments in the wrong order. The range you want to add up goes first:

Wrong: =SUMIFS(Transactions!$B2:$B1000, "=2020_10", Transactions!$H2:$H1000, "=Salary", Transactions!$E2:$E1000)

Correct: =SUMIFS(Transactions!$E2:$E1000, Transactions!$B2:$B1000, "=2020_10", Transactions!$H2:$H1000, "=Salary")

This is because I adapted an existing SUMIF (singular), which takes the range you want to add up first. Why did they swap them lol.