|
Lookup Two Criteria using SUMPRODUCT
Thanks for the reply,
I had forgotten about the best solution for this:
Sometimes I get stuck on the lookup formulas I forget that there is SUmproduct
this one works for both columns
Code:
column H
=SUMPRODUCT(($B$5:$B$25=G5)*($A$5:$A$25=$H$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G6)*($A$5:$A$25=$H$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G7)*($A$5:$A$25=$H$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G8)*($A$5:$A$25=$H$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G9)*($A$5:$A$25=$H$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G10)*($A$5:$A$25=$H$4)*($C$5:$C$25))
column I
=SUMPRODUCT(($B$5:$B$25=G5)*($A$5:$A$25=$I$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G6)*($A$5:$A$25=$I$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G7)*($A$5:$A$25=$I$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G8)*($A$5:$A$25=$I$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G9)*($A$5:$A$25=$I$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G10)*($A$5:$A$25=$I$4)*($C$5:$C$25))
__________________
Dave
Please read the Forum Rules before posting!
Last edited by davesexcel; 11-09-2006 at 06:56 AM..
|