CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
F. Vosnim
F. Vosnim

Reputation: 574

How to convert Excel formula to Google Sheets?

This code calculates a sum of string lengths in the range H1:V5 using G as a row index and C as a column index. It works perfectly in Excel:

{=SUMPRODUCT(LEN(INDEX(H1:V5,N(IF({1},ROW(G2:G5))),N(IF({1},C2:C5+1))))*ISNUMBER(G2:G5))}

enter image description here

But when I try it in Google Sheets it doesn't work although Google Sheets recognizes all commands. Is it possible to convert my formula to Google Sheets? Or maybe there is some workaround to get the same result there?

Open with Google Sheets returns incorrect result:

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUMPRODUCT(LEN(INDEX(H1:V5,N(IF({1},ROW(G2:G5))),N(IF({1},C2:C5+1))))*ISNUMBER(G2:G5))), 1, 1)

Upvotes: 0

Views: 193

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34180

Unfortunately the construct index...n(if({1}... is peculiar to Excel.

The vlookup function in Google sheets is very versatile and you can use that instead:

=SUMPRODUCT(len(vlookup(row(H1:K5),{row(H1:K5),H1:K5},C1:C5+2,false))*isnumber(G1:G5))

enter image description here

Upvotes: 3

Related Questions