Array Formula To Return List Based on Multiple Conditions

Question

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

barry houdini · Accepted Answer · 2015-03-10 20:35:27Z

4

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

edited Mar 10, 2015 at 20:35

answered Mar 9, 2015 at 23:08

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kyle Over a year ago

Thanks! This is exactly what I was looking for. No And() or Or() in arrays, good to know...

Gary's Student · Accepted Answer · 2015-03-09 21:51:18Z

2

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

answered Mar 9, 2015 at 21:51

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

Michael Foster · Accepted Answer · 2015-03-09 21:57:09Z

1

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.

answered Mar 9, 2015 at 21:57

Michael Foster

3861 gold badge7 silver badges17 bronze badges

Comments

pnuts · Accepted Answer · 2015-03-10 00:01:46Z

1

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.

answered Mar 10, 2015 at 0:01

pnuts

59.7k11 gold badges92 silver badges141 bronze badges

Collectives™ on Stack Overflow

Array Formula To Return List Based on Multiple Conditions

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related