Google Sheets Error "Array Arguments to Sumifs Are of Different Size"
Converting from an Excel File (Where This Works Fine), I Have a Sumifs Formula That Is Returning an Error "Array Arguments to Sumifs Are of Different Size"...
Converting from an Excel file (where this works fine), I have a SUMIFS formula that is returning an error "Array Arguments to SUMIFS are of different size". The formula in question looks like this:
=SUMIFS($G9:$EA9,$F$2:$DZ$2,">=1/1/"&A$2,$F$2:$DZ$2,"<=12/31/"&A$2)
The array arguments are:
- G9:EA9 - 125 columns, 1 row
- F2:DZ2 - 125 columns, 1 row
- F2:DZ2 - 125 columns, 1 row
The criteria arguments are values. I'm not looking for a workaround or hack - just want to know if I'm somehow misusing the SUMIFS formula so I can maintain consistency with Excel
2 Answers
This turns out to be a quirk of Google Sheets, which only generates as many columns as "needed". So while Excel understands what EA means even when there's no content there, by default Google Sheets thinks there are far fewer columns, and therefore the offset array ranges were indeed different sizes. 38 vs 39 in my case. When I added 125 columns to the Sheet, the formula worked fine.
Quick Fix - Make sure the columns in your criterion range are un-merged, as soon as I made them single columns it worked.
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
