ArrayFormula, SumProduct and Google Sheets returning only 1 value

0
votes

I have two sheets. I'm using SUMPRODUCT to sum a column based on a matching string.

=SUMPRODUCT(--(skus_campaign!A:A=A2),skus_campaign!D:D)))

This works exactly as expected, if I drag the formula to the rows below.

If I attach ARRAYFORMULA and and IF test to see if there's a blank value, it won't work.

=ARRAYFORMULA(IF(ISBLANK(A2:A), " ", SUMPRODUCT(--(skus_campaign!A:A=A2),skus_campaign!D:D)))

Am I missing something here? Is there an easier way to accomplish this while still using ARRAYFORMULA to grow and shrink the column based on the values in column A?

EDIT: Here's the link to the example Google Sheet. Column "D" under the "Data" sheet is the issue.

1
google-sheetsgoogle-sheets-formula
Can you share the spreadsheet to reproduce it? – Kessy
Let me remove some sensitive data and do that. Thanks. – Erik Olsen
Here's the link:docs.google.com/spreadsheets/d/… The problem lies in column "D" on the "Data" sheet. I can't get that ArrayFormula() to replicate the formula down the column, it only replicates the data from the first row. – Erik Olsen

1 Answers

1
votes

Try in E1

={"COGS"; ArrayFormula(if(len(A2:A), vlookup(A2:A, query(skus!A:D, "Select A, sum(D) where A <>'' group by A"), 2, 0),))}

and see if that produces the desired result. If it does, clear all values and formulas in column D and enter the formula in D1.