Multiple criteria array

[AndroidClone]

Hi guys, very long time lurker but first-time poster. I am wondering if anyone could help me clean up this array formula. I need to be able to count all instances of 0,1,2 except for [0,0],[0,1],[1,0],[1,1]. Please forgive my quick and dirty formula (not safe for looking). It is working as intended but is not user-friendly at all. I just added the + operator and copied in my original array for all of the other possible combinations that you can see on my pivot table (highlighted in yellow)


B2 = Country
BQ:BQ = P1 Country list
BG:BG = pass/fail left
BH:BH = pass/fail right
pass = 1
fail = 2
blank = 0

=SUM(IF((B2='P1 EXPORT'!$BQ$2:$BQ$1769)*('P1 EXPORT'!$BG$2:$BG$1769=2)*('P1 EXPORT'!BH2:$BH$1769=2),1/COUNTIFS('P1 EXPORT'!$BQ$2:$BQ$1769,B2,'P1 EXPORT'!$A$2:$A$1769,'P1 EXPORT'!$A$2:$A$1769,'P1 EXPORT'!$BG$2:$BG$1769,2,'P1 EXPORT'!$BH$2:$BH$1769,2)),0)+SUM(IF((B2='P1 EXPORT'!$BQ$2:$BQ$1769)*('P1 EXPORT'!$BG$2:$BG$1769=1)*('P1 EXPORT'!BH2:$BH$1769=2),1/COUNTIFS('P1 EXPORT'!$BQ$2:$BQ$1769,B2,'P1 EXPORT'!$A$2:$A$1769,'P1 EXPORT'!$A$2:$A$1769,'P1 EXPORT'!$BG$2:$BG$1769,1,'P1 EXPORT'!$BH$2:$BH$1769,2)),0)+SUM(IF((B2='P1 EXPORT'!$BQ$2:$BQ$1769)*('P1 EXPORT'!$BG$2:$BG$1769=0)*('P1 EXPORT'!BH2:$BH$1769=2),1/COUNTIFS('P1 EXPORT'!$BQ$2:$BQ$1769,B2,'P1 EXPORT'!$A$2:$A$1769,'P1 EXPORT'!$A$2:$A$1769,'P1 EXPORT'!$BG$2:$BG$1769,0,'P1 EXPORT'!$BH$2:$BH$1769,2)),0)+SUM(IF((B2='P1 EXPORT'!$BQ$2:$BQ$1769)*('P1 EXPORT'!$BG$2:$BG$1769=2)*('P1 EXPORT'!BH2:$BH$1769=1),1/COUNTIFS('P1 EXPORT'!$BQ$2:$BQ$1769,B2,'P1 EXPORT'!$A$2:$A$1769,'P1 EXPORT'!$A$2:$A$1769,'P1 EXPORT'!$BG$2:$BG$1769,2,'P1 EXPORT'!$BH$2:$BH$1769,1)),0)+SUM(IF((B2='P1 EXPORT'!$BQ$2:$BQ$1769)*('P1 EXPORT'!$BG$2:$BG$1769=2)*('P1 EXPORT'!BH2:$BH$1769=0),1/COUNTIFS('P1 EXPORT'!$BQ$2:$BQ$1769,B2,'P1 EXPORT'!$A$2:$A$1769,'P1 EXPORT'!$A$2:$A$1769,'P1 EXPORT'!$BG$2:$BG$1769,2,'P1 EXPORT'!$BH$2:$BH$1769,0)),0)

 

[Marcelo Branco]

Welcome to Mr Excel forum

Not sure i understand what you are looking for.
It seems that you need to count unique values in 'P1 EXPORT'!$A$2:$A$1769 if
'P1 EXPORT'!$BQ$2:$BQ$1769=B2
And
'P1 EXPORT'!$BG$2:$BG$1769 concatenated with 'P1 EXPORT'!BH2:$BH$1769 is either 22 or 12 or 21 or 02 or 20

Am i right?

M.

 

[AndroidClone]

@Marcelo

Correct! unfortunately my picture did not display right in my original posting. Here is the pivot table below.
https://imgur.com/a/DEBKkgj

 

[Marcelo Branco]

See if this example helps.
Goal: to count unique names in column A if:
Country=US;
And
C&D= either 22 or 12 or 21 or 02 or 20

[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]
[TD="bgcolor: #DCE6F1"]

F​

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

G​

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

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Country​

[/TD]
[TD]

Pass/Fail​

[/TD]
[TD]

Pass/Fail​

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

Country​

[/TD]
[TD]

UniqueNames​

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

2​

[/TD]
[TD]

Richard​

[/TD]
[TD]

AU​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

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

US​

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

4​

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

3​

[/TD]
[TD]

Mary​

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

US​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

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

4​

[/TD]
[TD]

Mark​

[/TD]
[TD]

BR​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

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

5​

[/TD]
[TD]

Barbara​

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

US​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

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

6​

[/TD]
[TD]

Robert​

[/TD]
[TD]

UK​

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

2​

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

2​

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

7​

[/TD]
[TD]

Mark​

[/TD]
[TD]

BR​

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

2​

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

2​

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

8​

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

Anthony​

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

US​

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

1​

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

2​

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

9​

[/TD]
[TD]

Charles​

[/TD]
[TD]

IT​

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

1​

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

2​

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

10​

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

Mike​

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

US​

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

2​

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

1​

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

11​

[/TD]
[TD]

Peter​

[/TD]
[TD]

FR​

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

2​

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

1​

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

12​

[/TD]
[TD]

Bob​

[/TD]
[TD]

FR​

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

0​

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

2​

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

13​

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

Bill​

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

US​

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

2​

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

0​

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

14​

[/TD]
[TD]

Albert​

[/TD]
[TD]

UK​

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

2​

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

1​

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

15​

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

Charles​

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

US​

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

2​

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

1​

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

16​

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

Charles​

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

US​

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

0​

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

2​

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

17​

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

Mike​

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

US​

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

2​

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

0​

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


Array formula in G2
=SUM(IF(FREQUENCY(IF(B$2:B$17=F2,IF(ISNUMBER(MATCH(C2:C17&D2:D17,{"22";"12";"21";"02";"20"},0)),MATCH(A$2:A$17,A$2:A$17,0))),ROW(A$2:A$17)-ROW(A$2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.