Working SUMPRODUCT function on Google Sheets does not work in Excel

1
votes

I have a function here that is meant to calculate how many leads I have in each country.

=sumproduct($A2=(vlookup(id_leads!$A:$A,country_leads!$A:$B,2,0)))

On sheet id_leads,$A:$A contains the lead ids

On sheet country_leads Column A contains the lead ids. Column B contains the country of which the lead is located.

Can someone explain why it fails in Excel (I get a #VALUE! error), but works fine in Google Sheets? A suggestion to make this formula work in Excel would be appreciated. I've tried to use COUNTIF and SUMIF, but couldn't figure it out.

Thanks in advance!

1
excelgoogle-sheetsvlookupcountifsumifs
Kindly provide an example of your data that we can copy/paste, or upload a copy of your dysfunctional workbook. Please read the Help section of this forum about How to create a Minimal, Complete, and Verifiable example. I suspect your problem is with the VLOOKUP function and its first argument, but your desired results should be easily obtainable in Excel. - Ron Rosenfeld

1 Answers

1
votes

You need to use COUNTIFS() wrap by SUM() in an array formula, something like:

=SUM(COUNTIFS(D:D,B1:B3,E:E,A1))

Being an array it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

enter image description here

Note the 2nd and 4th criteria need to be limited to the data set and not a full column reference.

So your formula would be something like this:

=SUM(COUNTIFS(country_leads!$A:$A,id_leads!$A1:$A100,country_leads!$B:$B,$A1))