Multiple lookup function

[Guest]

Hello,

I need some help with a multiple lookup function. I think there's a formula
that can be used as an array with either SUMIF or SUMPRODUCT but I can't
remember all the details.

Example:
A1 US
A2 California
A3 100

Formula: I want the formula to say "If A1 is US" and "if A2 = California,"
then 100.

Thanks in advance,
Scott

 

[MDubbelboer]

yup. sumproduct would work

=sumproduct(--(country range="US")*--(state range="California")*(las
range)

where country range is like A1:M1 or what have you

make sure your data cells don't have trailing spaces, you coul
wildcard (*) your criteria or trim if it does.

this is a good resource:
http://www.xldynamic.com/source/xld.SUMPRODUCT.htm

 

[SteveG]

Scott,
If your data continues accross (A1:Z1=Country, A2:Z2=State
A3:Z3=Value) then you could use,

=SUMPRODUCT((A1:Z1="US")*(A2:Z2="California")*(A3:Z3))

HTH

Stev