Hlookup and Sum

[Dave]

Is it possible to combine a hlookup function with a sum
function i.e. to look up a name in a table and return the
sum of the first n cells beneath it.

 

[Dan E]

Dave,

Sample
testA testB testA testA testB
1 2 1 2 2
400 300 350 450 500

If I wanted to sum all of the values with testA in row 1
=SUMPRODUCT((A1:E1="testA")*(A3:E3))
returns 1200

If I wanted to sum all with testA in row 1 and 1 in row 2
=SUMPRODUCT((A1:E1="testA")*(A2:E2=1)*(A3:E3))
returns 750

logic
returns True or False (1 or 0) for logical statements and
numbers for ranges for example
(A1:E1="testA") returns (1 0 1 1 0)
(A2:E2=1) returns (1 0 1 0 0)
(A3:E3) returns (400 300 350 450 500)

So for the first example returns
1*400 + 0*300 + 1*350 + 1*450 + 0*500 = 1200
for the second example
1*1*400 + 0*0*300 + 1*1*350 + 1*0*450 + 0*0*500 = 750

Dan E

 

[Don Guillett]

Try using the match function to find the row.