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

	A​	B​	C​	D​	E​	F​	G​
1​	Name​	Country​	Pass/Fail​	Pass/Fail​		Country​	UniqueNames​
2​	Richard​	AU​	0​	0​		US​	4​
3​	Mary​	US​	0​	1​
4​	Mark​	BR​	0​	0​
5​	Barbara​	US​	1​	1​
6​	Robert​	UK​	2​	2​
7​	Mark​	BR​	2​	2​
8​	Anthony​	US​	1​	2​
9​	Charles​	IT​	1​	2​
10​	Mike​	US​	2​	1​
11​	Peter​	FR​	2​	1​
12​	Bob​	FR​	0​	2​
13​	Bill​	US​	2​	0​
14​	Albert​	UK​	2​	1​
15​	Charles​	US​	2​	1​
16​	Charles​	US​	0​	2​
17​	Mike​	US​	2​	0​

<tbody>
</tbody>

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.