SUMIFS in an ARRAYFORMULA not working

Question

I'm creating a sales tracker and struggling with a SUMIFS ARRAYFORMULA.

This is the formula:-

=arrayformula(SUMIFS('LIVE!'!$C$4:$C,'LIVE!'!$K$4:$K,$C$14:$C,'LIVE!'!$J$4:$J,"Ex-Yard"))

This is a snapshot of the columns I'm looking at:

• Sheet 1 ("Customer Sales"): Column C = Account number, Column G = 'Ex-yard Totals'
• Sheet 2 ("Live!"): Column C = 'Sale value', Column j = "Ex-yard" / "Direct", Column K = Account number

The formula is going in Sheet 1, column G. I need it to sum the sale value column on sheet 2 (column C) for all rows that say "Ex-Yard" in column J, but only on rows where the account number in sheet 1 column C is found in sheet 2 column K.

It contains sensitive data so can't share but this is an example: https://docs.google.com/spreadsheets/d/1jcZNkg5lI7SFlZw153vNGSjOyGmwr3Q-xEq03AmVkeo/edit?usp=drivesdk

Thanks

Guy

Answer 1

Indeed Google Sheets seems to have problems with ARRAYFORMULA and SUMIFS if the first argument of the latter function is a range, i.e. the following formula will produce incorrect results (using the corrections suggested by @ttarchala):

=ARRAYFORMULA(IF(ISBLANK(C5:C), "", SUMIFS('LIVE!'!C$4:C,'LIVE!'!A$4:A,D5:D,'LIVE!'!J$4:J,"Ex-Yard")))

Another workaround, while still using ARRAYFORMULA, is replacing SUMIFS by SUMIF and concatenating the two columns being compared:

=ARRAYFORMULA(IF(ISBLANK(C5:C), "", SUMIF('LIVE!'!A$4:A&'LIVE!'!J$4:J,D5:D&"Ex-Yard",'LIVE!'!C$4:C)))

Now you don't have to copy the formula down in your sheet.

Answer 2

There are multiple problems with the formula.

First it's addressing the wrong columns in the SUMIFS: based on the spreadsheet you posted, the account number is nowhere in sheet 2, what you can make the link on seems the to be the customer name in column A instead. After fixing the addressing, this formula, when applied to a single cell in 'Customer sales'!G5, will produce a correct sum for that customer:

=if(isblank(C5),"",sumifs('LIVE!'!C$4:C,'LIVE!'!A$4:A,D5,'LIVE!'!J$4:J,"Ex-Yard"))

Second, for reasons only known to Google, ARRAYFORMULA does not work well with SUMIFS or other functions that process a range in their first argument. So sad news, you cannot use ARRAYFORMULA, instead you need to copy the formula down (which is the reason why you have to use absolute addressing with $ signs now).