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

SanFelippo

Board Regular
Joined
Apr 4, 2017
Messages
124
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.

Capture.JPG
Capture2.JPG


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.

Additional info:

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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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