CODEWITHSUNDEEP
google-sheetssumgoogle-sheets-formulaarray-formulas
barbarajohnson
barbarajohnson

Reputation: 47

Troubleshooting formula with array - array arguments are of different size to EQ

In Google Sheets, I have a formula that displays the value of an item in a row if one of its cells contains any of the values listed in a different sheet. It looks like this:

=ARRAYFORMULA(IF(OR(L2 = ZRSKUs!$A$1:$Z$12005), O2, "0"))

If L2 contains any of the values in sheet ZRSKUs, this formula displays the value of the item, which is held in O2. If I drag the formula down it produces the value of every column and I can then get a SUM of this column. I wanted a way to do this without having to drag the formula down every single row (this spreadsheet has about 20,000 rows so it takes a long time to do). I also wanted the formula to add it up too, so it is all done in one cell.

I tried editing the formula to do this, and this is what I came up with:

=ARRAYFORMULA(SUM(IF(OR($L3:$L = ZRSKUs!$A$1:$A$500), $O3:$O, "0")))

However, this gives me an "Array arguments to EQ are of different size" error. I tried adjusting the number of rows in the ZRSKUs sheets so it had the exact same number as my other sheet, but this made no difference.

I'm not sure what's going wrong, so any help or advice would be greatly appreciated!

Upvotes: 0

Views: 777

Answers (1)

AAA
AAA

Reputation: 3670

You get the error because that is not a well-formed array formula, as $L3:$L and ZRSKUs!$A$1:$A$500 are not equal in length. We could rectify this by using another function for the lookup, in this case, MATCH:

=ARRAYFORMULA(SUM(IF(ISNA(MATCH($L$1:$L, ZRSKUs!$A$1:$A$500, 0)), 0, $O$1:$O)))

Upvotes: 1

Related Questions