Conditional sum, when condition has multiple values
 

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

Conditional sum, when condition has multiple values
 

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

Conditional sum, when condition has multiple values
 

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

Conditional sum, when condition has multiple values
 

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