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!

Tags:

Best Answer

  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭
    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

  • Jake Gustafson
    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
    r0030 ✭✭✭✭
    edited 05/13/22

    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
    Jake Gustafson ✭✭✭✭✭✭
    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.

  • r0030
    r0030 ✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!