Uh...can this be done?

[christianbiker]

Greetings all,

The following formula works great using one column, however is it possible to use it with multiple columns. Here is my current formula:
=IF(COUNTIF(DATABASE!$J$1:$J$500,"Active")>=ROWS($1:1),INDEX(DATABASE!$A$1:$A$500,SMALL(IF(DATABASE!$J$1:$J$500="Active",ROW($1:$500)),ROW(1:1))),"")

Here is what I tried, but couldn't get to work:
=IF(COUNTIF(and(DATABASE!$J$1:$J$500,"Active",DATABASE!$K$1:$K$500,"Yes")>=ROWS($1:1),INDEX(DATABASE!$A$1:$A$500,SMALL(IF(and(DATABASE!$J$1:$J$500="Active",DATABASE!$K$1:$K$500,"Yes"),ROW($1:$500)),ROW(1:1))),"")

I thought this would work more like a sumproduct formula however I am using text, not numbers.

Any assistance would be appreciated...

Chad

 

[Andrew Poulsom]

Use multiplication for AND:

=IF((COUNTIF(DATABASE!$J$1:$J$500,"Active")*COUNTIF(DATABASE!$K$1:$K$500,"Yes"))>=ROWS($1:1),INDEX(DATABASE!$A$1:$A$500,SMALL(IF(((DATABASE!$J$1:$J$500="Active")*(DATABASE!$K$1:$K$500="Yes")),ROW($1:$500)),ROW(1:1))),"")

confirmed with Ctrl+Shift+Enter.

 

[sijpie]

Your logic is all wrong in your formula..
The and() formula is first being evaluated between the 4 items in its brackets.Not what you intend to do at all.

 

[xld]

TRy this

=IF(ISERROR(SMALL(IF((DATABASE!$J$1:$J$50="Active")*(DATABASE!$K$1:$K$50="Yes"),ROW($1:$50)),ROW(A1))),"",
INDEX(DATABASE!$A$1:$A$500,SMALL(IF((DATABASE!$J$1:$J$50="Active")*(DATABASE!$K$1:$K$50="Yes"),ROW($1:$50)),ROW(A1))))

 

[christianbiker]

Andrew & xld:
Thanks for your assistance. Both formulas worked. I definitely wouldn't have figured that out.

I am sure glad there is a forum such as this that provides real assistance!



sijpie:
Gee...Thanks for the insight, or lack thereof...

If I was getting the intended result I wouldn't be here asking for help would I? My logic is based on my limited knowledge of Excel and I couldn't figure it out.