Count Unique Values with Multiple Criteria in a Filtered List

CokeOrCrack

Board Regular
Joined
Dec 13, 2015
Messages
81
I have a table that tracks positions across multiple companies and years. I want to count the number of unique positions.

The difficulty is that the same position title can be with infinite companies (ie. "Associate" is a position at Company A and Company B, etc)

There are also multiple entries in the table for the same position due to the year (ie. "Associate" at "Company A" has a 2019 entry as well as a 2018 entry, etc)


*****Is there a formula (likely array) that can count the unique values with multiple criteria AND will count only the visible values in a filtered list?

Thanks

OJ
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try:


Book1
ABCDE
1TitleCompanyYearUnique Positions
2AssociateABC20197
4AssociateABC2018
5AssociateDEF2019
6AssociateDEF2019
7ManagerABC2018
8ManagerDEF2019
9SalespersonGHI2018
11SalespersonGHI2018
13Facilties ManagerABC2019
14
15
16
17
18
19
20
Sheet9
Cell Formulas
RangeFormula
E2{=SUM(SIGN(FREQUENCY(IF(SUBTOTAL(103,OFFSET(A2,ROW(A2:A20)-ROW(A2),0)),MATCH(A2:A20&"|"&B2:B20&"|"&C2:C20,A2:A20&"|"&B2:B20&"|"&C2:C20,0)),ROW(A2:A20)-ROW(A2)+1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Here's an example that returns a count of unique values in Column C, based on values in Column A that equal "X", and corresponding values in Column B that equal "Y", and based on filtered data...

Code:
=SUM(IF(FREQUENCY(IF(SUBTOTAL([COLOR=#ff0000]3[/COLOR],OFFSET(C2:C100,ROW(C2:C100)-ROW(C2),0,1))>0,IF(A2:A100="X",IF(B2:B100="Y",IF(LEN(C2:C100)>0,MATCH(C2:C100,C2:C100,0))))),ROW(C2:C100)-ROW(C2)+1)>0,1))

To exclude rows that have been manually hidden, change the 3 in red to 103. Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. Change the criteria and range accordingly.

Hope this helps!
 
Last edited:
Upvote 0
Eric & Domenic:

Thanks! Both work great.

I forgot to add in my original question that I would be needing to look by specific company as well and Domenic's formula already takes care of that.

Thanks Again

OJ
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top