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
-
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?
-
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)")
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. that 2nd formula you sent did the trick thank you SOOOOOO much!!!!
-
No problem! I'm glad I could help.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!