Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Look Up with Multiple Conditions
Hello,
Could you please help me with a formula to lookup data based on multiple variables ? Please see attached Thanks Excel Dumbo |
#2
|
|||
|
|||
Quote:
I'm not sure from your example what it is you're trying to achieve. Could you explain a little more? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
|
|||
|
|||
Thanks Dave. This is what I was looking for
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count for multiple conditions in multiple columns | Excel Worksheet Functions | |||
Setting multiple conditions to return a figure from multiple cells | Excel Discussion (Misc queries) | |||
SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
Combining Text from multiple cells under multiple conditions | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |