2 way lookups

[ah]

A B C
1 Country Rev Class Sales
2 (Denmark) 1 139,403.82
3 (Denmark) 2 179,950.00
4 (Finland) 1 10,633.44
5 (Finland) 2 40,297.50
6 (Norway) 1 143,035.28
7 (Norway) 2 109,740.00

I want to return the sales value where country = "Denmark" and rev class
="2" (in other words cell "c3")

I am usiing windows 2003

 

[N Harkawat]

=sumproduct(--(a1:a9999=3),--(b1:b9999="Denmark),(c1:c9999))

 

[ah]

Hi N Harkawat,

This worked great & your solution even allowed me throw in a third
variable. But I have a question, what does - - do.

=sumproduct(--(a1:a9999=3),--(b1:b9999="Denmark),(c1:c9999))

 

[Ola]

-- is the most common way to turn True or False into 1 or 0.
It's required in certain formulas, like SUMPRODUCT, to make it Calculable.

Ola Sandstrom

-True=-1, -False=0
--True=1, --False=0
True*1=1, False*1=0
True+0=1, False+0=0