Using CountIfs to track a column will multiple options
Hi everyone I have a multi select column that I am trying to use a countifs statement to count of instances of a name. However it only returns cells where only the name exist and nothing else. Does anyone know how to make it that it counts all instances? Formula below for example
=COUNTIFS({Training Project Task List Range 3}, "Aubrey Campbell", {Late Check}, @cell = "Late")
Best Answer
-
Try this:
=COUNTIFS({Training Project Task List Range 3}, HAS(@cell, "Aubrey Campbell"), {Late Check}, @cell = "Late")
Answers
-
For multiselect columns you have to include a contains formula in there. =COUNTIFS(CONTAINS("Aubrey Campbell", {Training Project Task List Range 3}), {Late Check}, @cell = "Late")
-
Try this:
=COUNTIFS({Training Project Task List Range 3}, HAS(@cell, "Aubrey Campbell"), {Late Check}, @cell = "Late")
-
Thank you all the suggestions worked very well!
I have another question, I would like to look for the average duration of rows which fit specific conditions. How would I begin creating a formula for this?
-
You would use AVG(COLLECT(.........)
=AVG(COLLECT({range to average}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, ............................))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!