Count Unique Values with Multiple Criteria in a Filtered List

[CokeOrCrack]

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

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	Title	Company	Year		Unique Positions
2	Associate	ABC	2019		7
4	Associate	ABC	2018
5	Associate	DEF	2019
6	Associate	DEF	2019
7	Manager	ABC	2018
8	Manager	DEF	2019
9	Salesperson	GHI	2018
11	Salesperson	GHI	2018
13	Facilties Manager	ABC	2019
14
15
16
17
18
19
20
Sheet9

Cell Formulas
Range	Formula
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.

 

[Domenic]

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...

=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!

 

[CokeOrCrack]

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