Count Non Duplicate Values with matching criteria in another column

Dokat

Board Regular
Joined
Jan 19, 2015
Messages
135
Office Version
  1. 365
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?

ACD
GSF11-BLKInactive - Use Up InventoryKeep
GSF11-BLUActive-Purchased ItemKeep
GSFG11-BLKActive-Purchased ItemKeep
GSM11-BLUActive-Purchased ItemKeep
GSFG11-REDActive-Purchased ItemKeep
GSM11-BLKActive-Purchased ItemKeep
GSM11-BLKInactive - Use Up InventoryKeep
GSM11-BLKActive-Purchased ItemKeep
GSM11-BLUInactive - Use Up InventoryKeep
GSM11-BLUInactive - Use Up InventoryKeep
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 19, 2015
Messages
135
Office Version
  1. 365
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
In that case how do you decide what is a duplicate value?
 

Dokat

Board Regular
Joined
Jan 19, 2015
Messages
135
Office Version
  1. 365

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
So if a value in A occurs more than once, regardless of the values in C & D it should be ignored?
 

Dokat

Board Regular
Joined
Jan 19, 2015
Messages
135
Office Version
  1. 365

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff v2.xlsm
ABCDEF
1
2GSF11-BLKInactive - Use Up InventoryKeep1
3GSF11-BLUActive-Purchased ItemKeep
4GSFG11-BLKActive-Purchased ItemKeep
5GSM11-BLUActive-Purchased ItemKeep
6GSFG11-REDActive-Purchased ItemKeep
7GSM11-BLKActive-Purchased ItemKeep
8GSM11-BLKInactive - Use Up InventoryKeep
9GSM11-BLKActive-Purchased ItemKeep
10GSM11-BLUInactive - Use Up InventoryKeep
11GSM11-BLUInactive - Use Up InventoryKeep
Summary
Cell Formulas
RangeFormula
F2F2=COUNT(FILTER(ROW(A2:A11),(C2:C11=C2)*(D2:D11=D2)*(ISNUMBER(XMATCH(A2:A11,UNIQUE(A2:A11,,1),0)))))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,251
Messages
5,600,540
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top