How to count when you have multiple crteria in 2 different columns you are trying to capture?

=COUNTIFS("[Interested Volunteer Role]12:[Interested Volunteer Role]85, "Vaccinator (Clinical)", [Interested Volunteer Role]14:[Interested Volunteer Role]82, "Dunkin Donuts Park-Hartford, CT 11/14/21 (Dose 1); 10am-4pm")


So for example if they chose they want to be a Vaccinator (Clinical) but can only help on one date which is in another column how do i capture that?

Answers

  • Update Forumla to: =COUNTIFS([Interested Volunteer Role]12:[Interested Volunteer Role]85, "Vaccinator (Clinical)", [Interested Volunteer Role]14:[Interested Volunteer Role]82, "Dunkin Donuts Park-Hartford, CT 11/14/21 (Dose 1); 10am-4pm") & I am receiving a #incorrect argument set

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    Hi @Elizabeth Anderson ,

    You are pulling the same columns for both criteria, but different ranges. Maybe you are meaning to pull another column that is date/location for the second criteria set? You also might have better performance using [Interested Volunteer Role]:[Interested Volunteer Role] without the row numbers to pull the full data set.

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • @hollyconradsmith thanks for your response! Its still not working, I also added the contains as you advised:


    =COUNTIF([Date/Venue of Interest]:[Date/Venue of Interest], CONTAINS("Dunkin Donuts Park-Hartford, CT 11/14/21 (Dose 1);10am-4pm", [Date/Venue of Interest]:[Date/Venue of Interest]), "Vaccinator(Clinical)")


    So Date/Venue of Interest is one Column & Interested Volunteer Role is the other column but I'm getting

    Unparseable I want the formula to count as 1 if the person submits say the 11/14 date & the role of Vaccinator if that makes sense?

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    Hi @Elizabeth Anderson To use 2 different criteria, you would need to use "CountifS" and the second criteria needs to pull from the other column range. ie:

    =COUNTIFS([Date/Venue of Interest]:[Date/Venue of Interest], contains("insert venue name here", [Date/Venue of Interest]:[Date/Venue of Interest]), [role column name]:[role column name], "insert role name here")

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • @hollyconradsmith thank you! So I just tried:


    =COUNTIFS([Date/Venue of Interest]:[Date/Venue of Interest], contains("Dunkin Donuts Park-Hartford, CT 11/14/21 (Dose 1); 10am-4pm", [Date/Venue of Interest]:[Date/Venue of Interest]), [role column name]:[role column name], "Vaccinator (Clinical)")


    I'm still getting the unparseable

  • Also tried the below & still getting unparseable


    =COUNTIFS([Date/Venue of Interest]:[Date/Venue of Interest], CONTAINS("Dunkin Donuts Park-Hartford, CT 11/14/21 (Dose 1); 10am-4pm", [role column name]:[role column name], "Vaccinator (Clinical)")

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Elizabeth Anderson

    When you list the range again inside the CONTAINS, try using @cell instead:

    =COUNTIFS([Date/Venue of Interest]:[Date/Venue of Interest], CONTAINS("Dunkin Donuts Park-Hartford, CT 11/14/21 (Dose 1); 10am-4pm", @cell), [role column name]:[role column name], "Vaccinator (Clinical)")


    It also seems like you have just text inserted in the [role column name] column. This is how a COUNTIFS works:

    =COUNTIFS([Column 1]:[Column 1], "Criteria 1", [Column 2]:[Column 2], "Criteria 2")


    For the second half of your formula, you will need to list the column name that contains the words "Vaccinator (Clinical)". For example, is that your "Interested Volunteer Role" column?

    You'll need to update it like so:

    =COUNTIFS([Date/Venue of Interest]:[Date/Venue of Interest], CONTAINS("Dunkin Donuts Park-Hartford, CT 11/14/21 (Dose 1); 10am-4pm", @cell), [Interested Volunteer Role]:[Interested Volunteer Role], "Vaccinator (Clinical)")


    Cheers!

    Genevieve

  • @Genevieve P. that 2nd formula you sent did the trick thank you SOOOOOO much!!!!

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! I'm glad I could help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!