sumproduct sumifs -- formula works in excel not in google sheet-CodePudding

I have been struggling with a formula that works in excel but not in google sheet. Objective is to get the sum of amounts provided in different currencies.

=SUMPRODUCT(B7:B12,SUMIFS(F:F,E:E,C7:C12))

B7:B12 contains amounts in different currencies C7:C12 contains the currency code for each amount E has the list of currency codes F has the exchange rate for each currency thank you much for any help/support!

whereas if you try the same conditions with Sumif you get:

=ArrayFormula(sumif(E:E,C7:C12,F:F))

which is why you get the error.

One way to fix it is to use Sumif instead of sumifs:

=ArrayFormula(sumproduct(B7:B12,sumif(E:E,C7:C12,F:F)))

EDIT

Array formula can be omitted if wrapped in sumproduct:

=sumproduct(B7:B12,sumif(E:E,C7:C12,F:F))

CodePudding user response：

=SUMPRODUCT(VLOOKUP(B1:B6,D1:E2,2,1)*A1:A6)

works in Google Sheet instead of

=SUMPRODUCT(B7:B12,SUMIFS(F:F,E:E,C7:C12))