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

0
votes

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!

sample data -- exactly the same data and formula in XL and GS

2
excelgoogle-sheets-formulasumifssumproduct
Do you have actual sample data and expected results to work with? – JvdV
In google sheet it will depends on SUMIFS() result. Sumifs() result must be same size of first array of sumproduct. – Harun24HR
Are the arguments in sumifs() the same in googlesheets as in Excel? – Solar Mike
=SUMPRODUCT(VLOOKUP(B1:B6,D1:E2,2,1)*A1:A6) then? – JvdV
@JvdV :-) Thank you so much, this is it. Much appreciated. – jmw

2 Answers

1
votes

Unfortunately at time of writing Sumifs doesn't expand in Google Sheets even when wrapped in Arrayformula. If you try it on its own you get:

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

enter image description here

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

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

enter image description here

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)))

enter image description here

EDIT

Array formula can be omitted if wrapped in sumproduct:

=sumproduct(B7:B12,sumif(E:E,C7:C12,F:F))
0
votes
=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))