Google Sheets error: Array arguments to sumifs are of different size

[rpadmana]

I'm using google sheets and getting the error: Array Arguments to SUMIFS are of different size. I'm using the formula below to sum a varying column in a database based on the year and the quarter in the summary sheet. I believe the error comes because my sum range is different than my criteria range, but they will never be the same because the sum range will constantly increase as the database expands. Is there a better way to do this to allow an expanding dataset? Thank you!


=sumifs(index(Transactions!$K$9:$DG$9,match($A10,Transactions!$K$8:$DG$8,0)),Transactions!$B$9:$B$424,BL$6,Transactions!$C$9:$C$424,BL$7)

 

[Luke M]

Hi rpadmana, and welcome to the forum!

Your sum range appears to be horizontal, and only hundred columns big, while your criteria ranges are over 400 cells big. I don't think you've got things setup the way you think you do.

Where is the data that you actually want to sum? Is it only in row 9, or is it actually in 9:424? Taking a guess at the correction:

=SUMIFS(INDEX(Transactions!$K$9:$DG$424,,MATCH($A10,Transactions!$K$8:$DG$8,0)),Transactions!$B$9:$B$424,BL$6,Transactions!$C$9:$C$424,BL$7)

 

[rpadmana]

The data is in the Transactions tab. The database is A8:DG424, but technically the values are from K9:DG424 (row 8 is column headers, and columns A:J are date, year, month, quarter, account desc, etc). The summary tab is a balance sheet, so the indexmatch is calling an account (cash, accts receivable, etc.) and finding that column in the database. It then sums the values in that column if the transaction falls within the year (BL6) and the quarter (BL7).

 

[rpadmana]

Changing the index portion to K9:DG424 does not remove the error

 

[bobhc]

As there are differences between Excel and Google Sheets, and this is an Excel site have you tried for help on a Google Sheets site.

 

[Luke M]

Did you also put in the extra comma? It's not simply changing the range, need to specify that you are giving a column argument, not a row argument to the INDEX function.