CODEWITHSUNDEEP
excelgoogle-sheetsvlookupcountifsumifs
Thomas Price
Thomas Price

Reputation: 101

Working SUMPRODUCT function on Google Sheets does not work in Excel

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!

Upvotes: 1

Views: 1141

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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

Upvotes: 1

Related Questions