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

Elizabeth Anderson

=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?

Elizabeth Anderson

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

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.

Elizabeth Anderson

@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

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")

Elizabeth Anderson

@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

Elizabeth Anderson

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.

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

Elizabeth Anderson

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

Genevieve P.

No problem! I'm glad I could help.