Count with multiple criteria and exclude duplicates

[nasee95]

I want to count the data in column D by excluding duplicate value with multiple criteria.
For example, I would like to count data excluding duplicate in column D with criteria of the column B=M2X-1, and column L=crack.
If I manually remove the duplicate, the result of count should be 2.
Since I am working on series of data and this is only small part of it which consume my time to filter out manually.
Hope you guys can help me with this.

Thank you in advance

Wire Summary 2022.xlsx
	B		D								L
27	AGNI-10		2121977/2-001A								Dented on shank
28	AGNI-10		2122052/3-001A								Dented on shank
29	NEOTACT-ZX		2121708/2-001A								Crack
30	M2X-1		2121050/1-001A								Crack
31	M2X-1		2121049/1-001A								Crack
32	M2X-1		2121050/1-001A								Crack
33	M2X-1		2121050/1-001A								Crack
34	M2X-1		2121049/1-001A								Crack
35	M2X-1		2121692/1-001A								Crack
Jan 21

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:D63,K:K,C89:D1048576,C86:C88,C65:D85,C64	Cell Value	duplicates	text	NO

 

[AhoyNC]

Shouldn't the answer be 3?
Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER

Book1
	B	C	D	E							L
21
22	Criteria 1	Criteria 2	Count Unique
23	M2X-1	Crack	3
24
25
26
27	AGNI-10		2121977/2-001A								Dented on shank
28	AGNI-10		2122052/3-001A								Dented on shank
29	NEOTACT-ZX		2121708/2-001A								Crack
30	M2X-1		2121050/1-001A								Crack
31	M2X-1		2121049/1-001A								Crack
32	M2X-1		2121050/1-001A								Crack
33	M2X-1		2121050/1-001A								Crack
34	M2X-1		2121049/1-001A								Crack
35	M2X-1		2121692/1-001A								Crack
Sheet1

Cell Formulas
Range	Formula
D23	D23	=SUM(IF(FREQUENCY(IF($D$27:$D$35<>"",IF($B$27:$B$35&"/"&$L$27:$L$35=$B$23&"/"&$C$23,MATCH($D$27:$D$35,$D$27:$D$35,0))),ROW($D$27:$D$35)-ROW($D$27)+1),1))

 

[Dermot]

or, a little simpler using spill formulae

=COUNTA(UNIQUE(FILTER(D27:D35,(B27:B35="M2X-1")*(L27:L35="Crack"))))

 

[Fluff]

@Dermot the users profile shows 2010 & therefore does not have Unique or Filter

 

[Dermot]

@Dermot the users profile shows 2010 & therefore does not have Unique or Filter

Ah

 

[nasee95]

Shouldn't the answer be 3?
Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER

Book1
	B	C	D	E							L
21
22	Criteria 1	Criteria 2	Count Unique
23	M2X-1	Crack	3
24
25
26
27	AGNI-10		2121977/2-001A								Dented on shank
28	AGNI-10		2122052/3-001A								Dented on shank
29	NEOTACT-ZX		2121708/2-001A								Crack
30	M2X-1		2121050/1-001A								Crack
31	M2X-1		2121049/1-001A								Crack
32	M2X-1		2121050/1-001A								Crack
33	M2X-1		2121050/1-001A								Crack
34	M2X-1		2121049/1-001A								Crack
35	M2X-1		2121692/1-001A								Crack
Sheet1

Cell Formulas
Range	Formula
D23	D23	=SUM(IF(FREQUENCY(IF($D$27:$D$35<>"",IF($B$27:$B$35&"/"&$L$27:$L$35=$B$23&"/"&$C$23,MATCH($D$27:$D$35,$D$27:$D$35,0))),ROW($D$27:$D$35)-ROW($D$27)+1),1))

Yes, The answer should be 3. My bad. I tried your solution, and it works. Thank you so much!

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[umaralikhan]

or, a little simpler using spill formulae

=COUNTA(UNIQUE(FILTER(D27:D35,(B27:B35="M2X-1")*(L27:L35="Crack"))))

Is this an array formula? Do I need to do ctrl+shift+enter?

 

[Fluff]

Is this an array formula?

Yes (but not array entered)

Do I need to do ctrl+shift+enter?

With 365 you never need to use Ctrl Shift Enter.

 

[umaralikhan]

Thank you for the quick response. I was not aware that array formulae could be used in office 365 without the need for ctrl+shift+enter.