Formula Question - Pulling in Multiple Criteria
I currently have a couple COUNTIFS formulas where I'm capturing the type by person by month as my criteria. I need to keep this criteria but add the condition that if multiple workers is selected in the source sheet (in a drop down with 3 options), to count the number in the correlating # of multiples column. The problem I've run into is I don't need it to just count that number, but take that number and + it with the other criteria to count.
In summary, I am trying to count the number of columns a person appears within my criteria + pull in the multiples column to add in that specific number (and not just count as 1).
Hopefully this makes sense. Any insight is greatly appreciated!
Best Answer
-
So the count of how many line items per Project Manager is =COUNTIF([Assigned To]:[Assigned To], <name reference>), right?
Then the sum of the multiple workers could be done a few different ways depending on how your data is structured. If you have the '# of Workers' field defaulted to 1 when 'Single Worker' is selected, then you should be able to do a SUMIFs.
=SUMIFS([# of Workers]:[# of Workers], [Assigned To]:[Assigned To], <name reference>)
However, another approach would be to use a combination of COUNTIFS and SUMIFS.
=COUNTIFS([Single Worker or Multiple Workers]:[Single Worker or Multiple Workers], "Single", [Assigned To]:[Assigned To], <name reference) + SUMIFS([# of Workers]:[# of Workers], [Single Worker or Multiple Workers]:[Single Worker or Multiple Workers], "Multiple", [Assigned To]:[Assigned To], <name reference>)
Not sure if I'm still missing something, but hopefully we are getting closer. Have a great weekend.
Answers
-
I'm a little confused by your information, but I'll give it my best shot. I'm wondering if you need to use the CONTAINS function to deal with that multi-select field. You may need to share a little more to understand your data better.
-
Thanks for the speedy response, Jake! Apologies for the confusion - I need more coffee! Below is an example of how my sheet is set up:
I'm trying to capture the amount of work that a Project Manager is doing. In this scenario, the tracker information is fed from a form. In the form, there is a drop down of if this is for one worker or multiple workers spread across one client. My formula is currently set up to capture how many line items a Project Manger is assigned to, but I would also like to include the # of workers if that is available, as this is an important part of capacity calculation.
As a side note, when an end user specifies that there are multiple workers involved with a client, we have them attach an excel sheet to detail out the request for all workers instead of having to submit a separate form every time. The # of workers column helps us better understand the work that needs to happen on our end to process the request.
-
So the count of how many line items per Project Manager is =COUNTIF([Assigned To]:[Assigned To], <name reference>), right?
Then the sum of the multiple workers could be done a few different ways depending on how your data is structured. If you have the '# of Workers' field defaulted to 1 when 'Single Worker' is selected, then you should be able to do a SUMIFs.
=SUMIFS([# of Workers]:[# of Workers], [Assigned To]:[Assigned To], <name reference>)
However, another approach would be to use a combination of COUNTIFS and SUMIFS.
=COUNTIFS([Single Worker or Multiple Workers]:[Single Worker or Multiple Workers], "Single", [Assigned To]:[Assigned To], <name reference) + SUMIFS([# of Workers]:[# of Workers], [Single Worker or Multiple Workers]:[Single Worker or Multiple Workers], "Multiple", [Assigned To]:[Assigned To], <name reference>)
Not sure if I'm still missing something, but hopefully we are getting closer. Have a great weekend.
-
I think this might just work! Appreciate your help, Jake!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!