6

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

Scott
  • 3,663
  • 8
  • 33
  • 56

2 Answers2

9

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.

Scott
  • 3,663
  • 8
  • 33
  • 56
  • The amount of rows in all the sheets has to match. This can be a problem, if you used a bad ArrayFormula in my case. You can just delete the extra rows, e.g. when you fixed the ArrayFormula. – AdamKalisz Jan 13 '22 at 09:55
0

Quick Fix - Make sure the columns in your criterion range are un-merged, as soon as I made them single columns it worked.

Jade Olayy
  • 59
  • 5