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 D2:D13, 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")*(D2:D13)) 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")*(D2:D13))

returns 100, not 200

Any ideas?

Jan

This seems to work for me (normally entered):

=SUMPRODUCT(ISNUMBER(MATCH(A2:A13,E2:E4,0))*(B2:B1 3="Industry")*(C2:C13="A")*(D2:D13))

Hope this helps.

Pete

On Oct 27, 10:42*pm, wrote:
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 D2:D13, 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")*(D2:D13)) 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")*(D2:D13))

returns 100, not 200

Any ideas?

Jan

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

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