Conditional sum, when condition has multiple values

[jkr]

I have a spreadsheet with five columns: A: Country, B:Sector,C: Rank, D: Valu and E:Approved

It looks something like this
Country Sector Rating Value Approved
Denmark Agriculture A 100 Denmark
Germany Industry A 100 Germany
Sweden Agriculture C 100 Italien
Norway Trade A 100
Italy Agriculture C 100
USA Industry A 100
China Trade A 100
Japan Industry A 100
Denmark Industry A 100
Germany Trade B 100
Belgium Agriculture B 100
Germany Agriculture A 100

Now I want to sum column D213, if B2:B13="Industry", C2:C13"A and the countries are in the approved list. In this case the result should be 200 as Denmark has Rank A Industry and so has Germany and USA, but USA is not in the approved list.

I have tried something like:

=SUMPRODUCT((A2:A13=E2:E4)*(B2:B13="Industry")*(C2:C13="A")*(D213)) but this returns N/A even if I enter it as an array formula.

=SUMPRODUCT((A2:A13=VLOOKUP(A2:A13,E2:E4,1,FALSE))*(B2:B13="Industry")*(C2:C13="A")*(D213))

returns 100, not 200

Any ideas?

Jan

 

[Pete_UK]

This seems to work for me (normally entered):

=SUMPRODUCT(ISNUMBER(MATCH(A2:A13,E2:E4,0))*(B2:B13="Industry")*(C2:C13="A")*(D213))

Hope this helps.

Pete

 

[jkr]

Thank you, that did the trick. Why the ISNUMBER() on the MATCH() function?

 

[jkr]

Don't bother, I figured out for my self that we only want to count the rows, where MATCH actually retunes a number.