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

[SanFelippo]

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.

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!

 

[Devo182]

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]

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]

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.