CODEWITHSUNDEEP
excelexcel-formula
Kyle
Kyle

Reputation: 2545

Array Formula To Return List Based on Multiple Conditions

I have a spreadsheet that looks like the picture below. I have some formulas that perform counts on this sheet and compare this data to another data source. An example of one of these would be:

=Countifs(A2:A10676,"0",C2:C10676,"OPEN",D2:D10676,"Current")

How would I return the list of loan numbers associated with the count? For instance, the count above returns 3038. I tried the below formula. I selected C2:C3039, typed the formula and hit Shift + Ctrl + Enter:

=If(And(A2:A10676="0",C2:C10676="OPEN",D2:D10676="Current"),B2:B10676,"")

My thought was that this evaluates the logical for each line and builds an array of the values in column B. When I enter this, each cell in the array is blank.

Can someone please explain how to return an array with values based on multiple criteria?

enter image description here

Upvotes: 0

Views: 12970

Answers (4)

barry houdini
barry houdini

Reputation: 46331

I see two problems with your formula

=If(And(A2:A10676="0",C2:C10676="OPEN",D2:D10676="Current"),B2:B10676,"")

Firstly you can't use AND to return an array - AND (like OR) returns a single result (TRUE or FALSE), so you need either nested IFs or to use * to simulate AND, i.e. either

=IF(A2:A10676=0,IF(C2:C10676="OPEN",IF(D2:D10676="Current",B2:B10676,"")))

....or.....

=IF((A2:A10676=0)*(C2:C10676="OPEN")*(D2:D10676="Current"),B2:B10676,"")

Note: I used 0 without quotes for the first criteria - for COUNTIFS you can use "0" or 0 but here it needs to match the data type - only use quotes if the data is text formatted - I'm assuming that isn't the case

The second problem is that for both of those the resulting array still has 10675 values because it still includes all the blanks for rows when the criteria aren't met.

To get an array of just 3038 values you can use this formula array entered into the correct sized range:

=INDEX(B2:B10676,SMALL(IF(A2:A10676=0,IF(C2:C10676="OPEN",IF(D2:D10676="Current",ROW(B2:B10676)-ROW(B2)+1)),ROW(INDIRECT("1:"&E2))))

Where E2 contains your COUNTIFS formula

Note that this only works to return an array in worksheet range - it won't work to return an array to be used in another function

Upvotes: 4

pnuts
pnuts

Reputation: 59450

Insert four blank rows at the top, copy A5:D6 into A1 and delete B2. DATA > Sort & Filter, Advanced, Copy to another location, List range: A5:D10680, Criteria range: A1:D2, Copy to: F1:I1, check Unique records only, OK.

Upvotes: 1

Michael Foster
Michael Foster

Reputation: 356

You can set a data filter:

Dim wrk As Worksheet
Set wrk = ActiveSheet
If Not wrk.AutoFilterMode Then wrk.range("a1").AutoFilter
wrk.range("a1").AutoFilter field:=1, Criteria1:="0"
wrk.range("a1").AutoFilter field:=3, Criteria1:="OPEN"
wrk.range("a1").AutoFilter field:=4, Criteria1:="Current"
Set wrk = Nothing

Of course, you could manually turn on the filters to how you like, too.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Here is an approach that uses a "helper column" rather than an array formula:

enter image description here

In E2 enter the formula:

=IF(AND(A2=0,C2="OPEN",D2="Current"),1+MAX($E$1:E1),"")

and copy down (this marks the multiple rows meeting the criteria)

In F2 enter:

=IFERROR(INDEX(B$2:B$24,MATCH(ROWS($1:1),$E$2:$E$24,0)),"")

and copy down.

enter image description here

Upvotes: 2

Related Questions