Query on Formula Forensics No. 003 – Lukes Reward

[Dee]

Dear All,


This is a query on one of the Lukes's post in Chandoo.org (Formula Forensics No. 003 – Lukes Reward).

Here in the specified formula we can have only one condition can be fulfilled ie., in the below example it is cell D2. what if i need to add more that one condition say <2 .

{=IF(COUNTIF(A:A,$D$2)<ROWS($G$2:G2),"",INDEX(B:B,SMALL(IF(A:A=$D$2,ROW(B:B)),ROW(A1))))}


i tried to edit the formula but was not successful.

Luke, if you see this post...can you pls help me on this?


Thanks in advance,

Dee...

 

[Luke M]

Hi Dee!

If we need to add more conditions, we'll need to modify the parts of the formula that are controlling the "condition met" parts. Currently, this is the COUNTIF at the beginning, and the IF statement at the end. For the former, we can replace the COUNTIF with a SUMPRODUCT to get our new count. That portion will become

SUMPRODUCT(--($A$1:$A$100>5),--($A$1:$A$100<=10))


The latter is a little trickier. We'll dealing with an IF function that uses an array. If we want our conditions to be of an "and" nature, we would multiply them like this:

IF((A:A=$D$2)*(A:A>2),...


if we want them to be of an "or" nature, we add them like so:

IF((A:A=$D$2)*(A:A>2),...


Altogether, the formula becomes:

{=IF(SUMPRODUCT(--($A$1:$A$100>5),--($A$1:$A$100<=10))<ROWS($G$2:G2),"",INDEX(B:B,SMALL(IF((A:A=$D$2)*(A:A>2),ROW(B:B)),ROW(A1))))}


Link for anyone else wanting to see original article:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/