Count Non Duplicate Values with matching criteria in another column

[Dokat]

Hi,

I have a range where I am trying to count only unique values with matching criteria in cell C.. In below examples I am trying to get the unique count in column A where C is "Inactive - Use Up Inventory" and column D is "Keep".The result should be 1 as GSF11-BLK is only unique value in column A where Column C is " Inactive - Use Up Inventory" and column D is "Keep". I tried array formulas as well as row unique filter formula however wasn't able to get the result. Did anyone come across a similar issue and have a solution for it?

A	C	D
GSF11-BLK	Inactive - Use Up Inventory	Keep
GSF11-BLU	Active-Purchased Item	Keep
GSFG11-BLK	Active-Purchased Item	Keep
GSM11-BLU	Active-Purchased Item	Keep
GSFG11-RED	Active-Purchased Item	Keep
GSM11-BLK	Active-Purchased Item	Keep
GSM11-BLK	Inactive - Use Up Inventory	Keep
GSM11-BLK	Active-Purchased Item	Keep
GSM11-BLU	Inactive - Use Up Inventory	Keep
GSM11-BLU	Inactive - Use Up Inventory	Keep

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

Also with that sample shouldn't the answer be 3 as you have GSF11-BLK, GSM11-BLK & GSM11-BLU

 

[Dokat]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

Also with that sample shouldn't the answer be 3 as you have GSF11-BLK, GSM11-BLK & GSM11-BLU

It is MS 365 for Enterprise. Output should be only 1 as GSM11-BLK & GSM11-BLU is duplicate values in the range.

Thanks

 

[Fluff]

In that case how do you decide what is a duplicate value?

 

[Dokat]

In that case how do you decide what is a duplicate value?

If an item in column A shows up multiple times it'ss considered duplicate value.

 

[Fluff]

So if a value in A occurs more than once, regardless of the values in C & D it should be ignored?

 

[Dokat]

So if a value in A occurs more than once, regardless of the values in C & D it should be ignored?

yes, correct

 

[Fluff]

Ok, how about

+Fluff v2.xlsm
	A	B	C	D	E	F
1
2	GSF11-BLK		Inactive - Use Up Inventory	Keep		1
3	GSF11-BLU		Active-Purchased Item	Keep
4	GSFG11-BLK		Active-Purchased Item	Keep
5	GSM11-BLU		Active-Purchased Item	Keep
6	GSFG11-RED		Active-Purchased Item	Keep
7	GSM11-BLK		Active-Purchased Item	Keep
8	GSM11-BLK		Inactive - Use Up Inventory	Keep
9	GSM11-BLK		Active-Purchased Item	Keep
10	GSM11-BLU		Inactive - Use Up Inventory	Keep
11	GSM11-BLU		Inactive - Use Up Inventory	Keep
Summary

Cell Formulas
Range	Formula
F2	F2	=COUNT(FILTER(ROW(A2:A11),(C2:C11=C2)*(D2:D11=D2)*(ISNUMBER(XMATCH(A2:A11,UNIQUE(A2:A11,,1),0)))))

 

[Dokat]

thank you it worked

 

[Fluff]

You're welcome & thanks for the feedback.