Returning a value if multiple conditions are met
in [Functions]
|
Prev: How can I copy the sum of a filtered column into a cell in anothe
Next: Date inconsistent help please :)
From: Joel on 27 May 2010 17:13 I am trying to develop a method which will return a value if each of three conditions are met. The three conditions, each with a differing number of possibilities, are set in three columns and for each permutation there is a value associated with it. I would then like to set up a drop down list for each condition from which to select the possibility of each and have the value for that combination of conditions returned to an adjacent cell. Example: Conditions >> A B C Value possibilities 1 x a 1 1 y a 2 1 x b 3 1 y b 4 2 x a 5 2 y a 6 2 x b 7 2 y b 8 Select: A B C Result >> 2 x b > 7 >> 1 y a > 2 Any help with this problem would be greatly appreciated.
From: Steve Dunn on 28 May 2010 10:57 Assuming your table in A2:D9, and your criteria in F2:H2 =index($D$2:$D$8,match(1,($A$2:$A$8=$F$2)* ($B$2:$B$8=$G$2)*($C$2:$C$8=$H$2,0)) should give the correct result. "Joel" <Joel(a)discussions.microsoft.com> wrote in message news:ACE3A568-7155-4BDC-A7B2-D640E3BBDD97(a)microsoft.com... >I am trying to develop a method which will return a value if each of three > conditions are met. The three conditions, each with a differing number of > possibilities, are set in three columns and for each permutation there is > a > value associated with it. > I would then like to set up a drop down list for each condition from which > to select the possibility of each and have the value for that combination > of > conditions returned to an adjacent cell. > Example: > Conditions >> A B C Value > possibilities 1 x a 1 > 1 y a 2 > 1 x b 3 > 1 y b 4 > 2 x a 5 > 2 y a 6 > 2 x b 7 > 2 y b 8 > > > Select: A B C Result > >> 2 x b > 7 > >> 1 y a > 2 > > Any help with this problem would be greatly appreciated.
From: Ashish Mathur on 28 May 2010 22:05
Hi, Try this. A2:C9 is your data range. A15:C15 is the select table =sumproduct(($A$2:$A$9=$A15)*($B$2:$B$9=$B15)*($C$2:$C$9=$C15)*($D$2:$D$9)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Joel" <Joel(a)discussions.microsoft.com> wrote in message news:ACE3A568-7155-4BDC-A7B2-D640E3BBDD97(a)microsoft.com... > I am trying to develop a method which will return a value if each of three > conditions are met. The three conditions, each with a differing number of > possibilities, are set in three columns and for each permutation there is > a > value associated with it. > I would then like to set up a drop down list for each condition from which > to select the possibility of each and have the value for that combination > of > conditions returned to an adjacent cell. > Example: > Conditions >> A B C Value > possibilities 1 x a 1 > 1 y a 2 > 1 x b 3 > 1 y b 4 > 2 x a 5 > 2 y a 6 > 2 x b 7 > 2 y b 8 > > > Select: A B C Result > >> 2 x b > 7 > >> 1 y a > 2 > > Any help with this problem would be greatly appreciated. |