Formula Question - Pulling in Multiple Criteria

r0030

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!

Jake Gustafson

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.

Jake Gustafson

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.

r0030

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.

Jake Gustafson

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.

r0030

I think this might just work! Appreciate your help, Jake!