Array formula with multiple Countifs

[neodjandre]

I am using this formula which works fine:

=IF(ROWS(K$62:K62)>SUM(COUNTIFS(accounts_table[§],{"P","B"})),"",INDEX(accounts_table[Account Name],SMALL(IF(ISNUMBER(MATCH(accounts_table[§],{"P","B"},0)),ROW(accounts_table[§])-ROW(INDEX(accounts_table[§],1,1))+1),ROWS($K$62:K62))))

However, I need to add another COUNTIFS criteria:

COUNTIFS(accounts_table[§],{"P","B"},accounts_table[on],"On")

If I only make this change, the function doesn't work as expected. I presume I also need to change the second part of the function but can't figure out how.

 

[neodjandre]

It it helps now, this is the structure of my spreadsheet

col A col B col C
P on item1
P off item2
B on item3
J on item4
P on item5
B off item6

So in the above example the array formula should pick item1, item3,item5

Hope it makes sense

 

[FormR]

Hi, try like this, committed with CTRL+SHIFT+ENTER.

=IF(ROWS(K$62:K62)>SUM(COUNTIFS(accounts_table[§],{"P","B"},accounts_table[on],"on")),"",INDEX(accounts_table[account name],SMALL(IF(accounts_table[on]="on",IF(ISNUMBER(MATCH(accounts_table[§],{"P","B"},0)),ROW(accounts_table[§])-ROW(INDEX(accounts_table[§],1,1))+1)),ROWS($K$62:K62))))

 

[Marcelo Branco]

EDIT
I hadn't seen FormR's formula that used table references. Solution already provided!

Try

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Header1​

[/TD]
[TD]

Header2​

[/TD]
[TD]

Header3​

[/TD]
[TD][/TD]
[TD]

List​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

P​

[/TD]
[TD]

on​

[/TD]
[TD]

item1​

[/TD]
[TD][/TD]
[TD]

item1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

P​

[/TD]
[TD]

off​

[/TD]
[TD]

item2​

[/TD]
[TD][/TD]
[TD]

item3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

B​

[/TD]
[TD]

on​

[/TD]
[TD]

item3​

[/TD]
[TD][/TD]
[TD]

item5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

J​

[/TD]
[TD]

on​

[/TD]
[TD]

item4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

P​

[/TD]
[TD]

on​

[/TD]
[TD]

item5​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

B​

[/TD]
[TD]

off​

[/TD]
[TD]

item6​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in E2 copied down
=IF(ROWS(E$2:E2)>SUM(COUNTIFS(A$2:A$7,{"P","B"},B$2:B$7,"On")),"",INDEX(C$2:C$7,SMALL(IF(ISNUMBER(MATCH(A$2:A$7,{"P","B"},0)),IF(B$2:B$7="on",ROW(C$2:C$7)-ROW(C$2)+1)),ROWS(E$2:E2))))
Ctrl+Shift+Enter

M.

 

[neodjandre]

Hi, try like this, committed with CTRL+SHIFT+ENTER.

=IF(ROWS(K$62:K62)>SUM(COUNTIFS(accounts_table[§],{"P","B"},accounts_table[on],"on")),"",INDEX(accounts_table[account name],SMALL(IF(accounts_table[on]="on",IF(ISNUMBER(MATCH(accounts_table[§],{"P","B"},0)),ROW(accounts_table[§])-ROW(INDEX(accounts_table[§],1,1))+1)),ROWS($K$62:K62))))

thanks this works as expected