Search code examples
excelgoogle-sheets-formulasumifssumproduct

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

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

Solution

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