Reply to thread

Message

[QUOTE="jkr, post: 7392349"]
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
[/QUOTE]

Verification