# Need a Unique Count of Last Names With Some Other Matching Criteria

#### SanFelippo

##### Board Regular
Hello,

What I have essentially is this. I have a Data set of services that have been provided by employees. Within this data set, I have employee Last Name, a Service Purpose Column, Services Provided Column, and a Quarter column.

As you can see in the set up, I have the formulas built to run based off the Quarter that is selected followed up with a countifs function.

Right now, all I can get the formula to do is return the total number rows that are matching the criteria you can see in the the formulas of the picture below. the 118 is the total numbers of rows, but there are employees who have multiple line items within each category. So there are 118 total rows that match the criteria, but really there are only say 27 Employees that are responsible for all those rows. I need my formula to return that 27 number, not the 118 it currently it. I am hoping to use the employee last name column in some way to achieve this.

Getting more complicated, within each Purpose you see on the table, there are the services provided (Advisor, Bank at Work, Board, Committee, Volunteer). I need the same thing as what we are trying to get with the 118 number, but then adding one more condition. For example, right now Affordable Housing, Board number of colleagues is returning 76, as there are 76 rows of data that are both Affordable housing AND Board. I need the unique count of of the last names that make up 76, not the total number of line items.

I don't want to use a pivot table because this data is always changing, so if there is a way to figure this out and do it with formulas, that would be awesome. I'm usually pretty good at this but I am kind of stumped here.

Employee Last Name - Column C
CRA Purpose (Affordable Housing, Community Service, Economic Development, Revitalization/Stabilization) - Column M
CD Services Provided (Advisor, Bank at Work, Board, Committee, Volunteer) - Column N
Quarter (Q1, Q2, Q3, Q4) - Column AD

Thanks!

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Devo182

##### New Member
Try sumproduct.

=SUMPRODUCT(1/COUNTIF(data,same_data))

You could also build out a secondary step of listing all the unique values using UNIQUE then do the countif off of that data set.

#### SanFelippo

##### Board Regular
Try sumproduct.

=SUMPRODUCT(1/COUNTIF(data,same_data))

You could also build out a secondary step of listing all the unique values using UNIQUE then do the countif off of that data set.
Unfortunately there are going to be a ton of employee last names and every time we get a new data set (which is probably weekly) there is the potential for there to be new ones. The idea is to avoid needing to go through and identify ew names every single time we do this. I need it to be a drop the new data in and it's finished type of process.

#### Devo182

##### New Member
Unfortunately there are going to be a ton of employee last names and every time we get a new data set (which is probably weekly) there is the potential for there to be new ones. The idea is to avoid needing to go through and identify ew names every single time we do this. I need it to be a drop the new data in and it's finished type of process.
If you do something like UNIQUE for the whole column(s) it will update automatically as new info is added, as would the count. Could also have the data be a table and reference that.

If you go the sumproduct route, you could make your data a table and reference the table, which will expand automatically with the new rows.

Replies
1
Views
472
Replies
4
Views
101
Replies
1
Views
118
Replies
1
Views
183
Replies
1
Views
527

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,972
Messages
5,767,394
Members
425,410
Latest member
SmittyT

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

### Which adblocker are you using?

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

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