# Count unique values in one column depending on multiple values in multiple columns

1. ## Count unique values in one column depending on multiple values in multiple columns

Essentially what I have is the following:

3 columns of data: "Managers", "Employees", and "Type". Each Employee shares a row with one Type and one Manager. Each employee may have multiple Managers and multiple Types (and thus be listed on multiple rows in the column). For clarification, a spreedsheet is attached.

What I need is an equation that counts the number of UNIQUE employees that are typeA, typeB, OR typeC (arbitrary), and who have either manager1 or manager3 (also arbitrary).

Filtering this data on the attached xlsx shows 9 rows of employees, but there are only 6 that are unique.

I would prefer an equation that uses SUMPRODUCT() rather than an array (for convenience), but I would be happy with with anything really.

Let me know if there is any confusion or questions. Much appreciated.

2. ## Re: Count unique values in one column depending on multiple values in multiple column

It's not a Sumproduct formula... but:

``Please Login or Register  to view this content.``
confirmed with CTRL+SHIFT+ENTER not just ENTER

Where F2 contains Manager to search and G2 contains Type to search...

3. ## Re: Count unique values in one column depending on multiple values in multiple column

If you want SUMPRODUCT, then first concatenate the 3 columns, in D2

=A2&B2&C2 copied down

and then try:

=SUMPRODUCT(((ISNUMBER(SEARCH(F2&G2,\$D\$2:\$D\$33))))/COUNTIF(\$D\$2:\$D\$33,\$D\$2:\$D\$33&""))

Where F2 contains Manager to search and G2 contains Type to search...

Same variable is previous post

4. ## Re: Count unique values in one column depending on multiple values in multiple column

This works well for 1 manager and 1 type. However, I need to search for unique employees of multiple types working for multiple managers.

Any suggestions?

Thanks.

5. ## Re: Count unique values in one column depending on multiple values in multiple column

I am not 100% sure I get you, and I've got to close down now....but perhaps you mean this?

If you have your list of Managers in F2:F3 and the corresponding Types you want in G2:G3, then try:

``Please Login or Register  to view this content.``
CSE confirmed

6. ## Re: Count unique values in one column depending on multiple values in multiple column

Yes, that's exactly what I need!

Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1