View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Dave O Dave O is offline
external usenet poster
 
Posts: 427
Default Look Up with Multiple Conditions

One possible answer:
=SUMPRODUCT(--($B$1=Data!$A$4:$A$21),--($A3=Data!$B$4:$B$21),Data!$C$4:$C$21)
....where Data!$c$4:$c$21 represents the column of numbers to be summed. To capture the July information you'd need to change this to Data!$d$4:$d$21.

This answer uses the double unary operator, indicated by the -- in the formula. In the context of SUMPRODUCT, the double unary operator checks a given column for a match, as in --($B$1=Data!$A$4:$A$21); where there is a match it applies a 1 to the multiplication, and where there is a mismatch it applies 0. If a row matches all the specified conditions, SUMPRODUCT multiplies 1 x 1 x N. If any condition mismatches the 0 multiplier yields a 0 in the sum.