Count Unique with multiple criteria excluding duplicate

[nikhil0311]

Hello Friends,
Need your help to count the IDs based on multiple criteria. If you guys can help me to create a formula to update output section (H4 to K5)

Thank you as always

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Product	LOB	Status			Count unique IDs based on multiple criterias
2	66545523	card	CCBSI	Active				Card		Receivables
3	66545523	card	CCBSI	Active		Output --->		Active	Inactive	Active	Inactive
4	11223344	receivables	CRE	Active			CCBSI	1	1	1	0
5	33445566	card	CRE	Active			CRE	2	0	1	1
6	11223344	card	CRE	Active
7	11223344	receivables	CRE	Inactive
8	66545523	card	CCBSI	Inactive
9	11223344	receivables	CCBSI	Active
10	33445566	receivables	CRE	Active
11	66545523	receivables	CRE	Inactive
Sheet1

 

[BigBeachBananas]

--Excluding duplicates oops--

 

[AhoyNC]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER .
Also, I get a different answer for CRE in the Receivables.

Drag formula down and across.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Product	LOB	Status			Count unique IDs based on multiple criterias
2	66545523	card	CCBSI	Active				Card	Card	Receivables	Receivables
3	66545523	card	CCBSI	Active		Output --->		Active	Inactive	Active	Inactive
4	11223344	receivables	CRE	Active			CCBSI	1	1	1	0
5	33445566	card	CRE	Active			CRE	2	0	2	2
6	11223344	card	CRE	Active
7	11223344	receivables	CRE	Inactive
8	66545523	card	CCBSI	Inactive
9	11223344	receivables	CCBSI	Active
10	33445566	receivables	CRE	Active
11	66545523	receivables	CRE	Inactive
Sheet1

Cell Formulas
Range	Formula
H4:K5	H4	=SUM(IF(FREQUENCY(IF($C$2:$C$11<>"",IF($C$2:$C$11=$G4,IF($D$2:$D$11=H$3,IF($B$2:$B$11=H$2,MATCH($A$2:$A$11,$A$2:$A$11,0))))),ROW($A$2:$A$11)-ROW($A$2)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[nikhil0311]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER .
Also, I get a different answer for CRE in the Receivables.

Drag formula down and across.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Product	LOB	Status			Count unique IDs based on multiple criterias
2	66545523	card	CCBSI	Active				Card	Card	Receivables	Receivables
3	66545523	card	CCBSI	Active		Output --->		Active	Inactive	Active	Inactive
4	11223344	receivables	CRE	Active			CCBSI	1	1	1	0
5	33445566	card	CRE	Active			CRE	2	0	2	2
6	11223344	card	CRE	Active
7	11223344	receivables	CRE	Inactive
8	66545523	card	CCBSI	Inactive
9	11223344	receivables	CCBSI	Active
10	33445566	receivables	CRE	Active
11	66545523	receivables	CRE	Inactive
Sheet1

Cell Formulas
Range	Formula
H4:K5	H4	=SUM(IF(FREQUENCY(IF($C$2:$C$11<>"",IF($C$2:$C$11=$G4,IF($D$2:$D$11=H$3,IF($B$2:$B$11=H$2,MATCH($A$2:$A$11,$A$2:$A$11,0))))),ROW($A$2:$A$11)-ROW($A$2)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

This is awesome, thank you. But this formula is slowing down excel performance. Every time I apply filter in the raw data it starts calculating threads.

 

[nikhil0311]

Is there a different formula to this that can improve excel performance?

 

[AhoyNC]

Here is another option using a helper column you might try. My guess is that this will be just as slow and maybe slower if you have a large data set.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Product	LOB	Status	Helper		Count unique IDs based on multiple criterias
2	66545523	card	CCBSI	Active	1			Card	Card	Receivables	Receivables
3	66545523	card	CCBSI	Active	2			Active	Inactive	Active	Inactive
4	11223344	receivables	CRE	Active	1		CCBSI	1	1	1	0
5	33445566	card	CRE	Active	1		CRE	2	0	2	2
6	11223344	card	CRE	Active	1
7	11223344	receivables	CRE	Inactive	1
8	66545523	card	CCBSI	Inactive	1
9	11223344	receivables	CCBSI	Active	1
10	33445566	receivables	CRE	Active	1
11	66545523	receivables	CRE	Inactive	1
Sheet1

Cell Formulas
Range	Formula
H4:K5	H4	=SUMPRODUCT(($C$2:$C$11=$G4)*($D$2:$D$11=H$3)*($E$2:$E$11=1)*($B$2:$B$11=H$2))
E2:E11	E2	=SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($C$2:C2=C2)*($D$2:D2=D2))

 

[Peter_SSs]

SUMPRODUCT can be very slow over large data sets. I have no idea how this would compare (also using a helper column which could be hidden) but you could give it a try.

23 11 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Product	LOB	Status	Helper
2	66545523	card	CCBSI	Active	66545523|card|CCBSI|Active			Card	Card	Receivables	Receivables
3	66545523	card	CCBSI	Active				Active	Inactive	Active	Inactive
4	11223344	receivables	CRE	Active	11223344|receivables|CRE|Active		CCBSI	1	1	1	0
5	33445566	card	CRE	Active	33445566|card|CRE|Active		CRE	2	0	2	2
6	11223344	card	CRE	Active	11223344|card|CRE|Active
7	11223344	receivables	CRE	Inactive	11223344|receivables|CRE|Inactive
8	66545523	card	CCBSI	Inactive	66545523|card|CCBSI|Inactive
9	11223344	receivables	CCBSI	Active	11223344|receivables|CCBSI|Active
10	33445566	receivables	CRE	Active	33445566|receivables|CRE|Active
11	66545523	receivables	CRE	Inactive	66545523|receivables|CRE|Inactive
Count

Cell Formulas
Range	Formula
H4:K5	H4	=COUNTIFS($E$2:$E$11,"*|"&H$2&"|"&$G4&"|"&H$3)
E2:E11	E2	=IF(ISNUMBER(MATCH(A2&"|"&B2&"|"&C2&"|"&D2,E$1:E1,0)),"",A2&"|"&B2&"|"&C2&"|"&D2)