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

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

On Oct 27, 10:42*pm, j...@knord.dk 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 D213, if B2:B13="Industry", C2:C13"A and thecountries 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

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.