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