COUNTIF CONTAINS(OR(
Answers
-
Kim - I'm thinking you might need to use the HAS function. That's new with the multi-select column feature. Maybe...there isn't alot of information out there about how to count this new column type. Or maybe COUNTM? Not totally sure your unique situation.
However, I have a COUNTIFS situation with a multi-select column that I can't figure out. I have a sheet with a list of project impacts and the corresponding audience that is impacted by this change. I have an intra-sheet formula that determines if something is entered in the Impact? column and returns a Y or N. I want to write a formula using the multi-select column that answers the question:
"How many times in this specific audience impacted?"
And I want it to count the number of times an audience is selected AND there is a Y in my Impact column. I hope the screen shots help.
I'm thinking this needs to be some kind of nested formula with COUNTIFS and HAS, but I cannot figure it out.
-
@Paul Newcome I was trying this same solution with my formula but obviously missing something:
=COUNTIFS([Status]@row, OR(CONTAINS("Initial Testing",@cell), CONTAINS("Ready for Retesting"@cell)))
I have a dropdown list in a column named "Status" where we track the progress in a process. The dropdown list contains the following choices:
Initial Testing
Ready for Review
Ready for Retesting
Ready for 2nd Review
App Support Request Submitted
Completed
ON HOLD
No Longer Needed
My situation is that I'd like to count the number of times that "Initial Testing" and "Ready for Retesting" are chosen for each row from the dropdown in the Status column. Unfortunately, this testing can go thru multiple cycles (Initial Testing, then Ready for Review, then Ready for Retesting, then Ready for 2nd Review, then Ready for Retesting, etc.). I was trying something like this at first...
=COUNTIF(Status@row, "Initial Testing") + COUNTIF(Status@row, "Ready for Retesting")
but it only counts "1" when either of the testing selections is made; it does not total the number of times those testing selections are chosen.
What am I missing?
-
@GMichal You would need to set up a column that has a unique identifier on every row. Then you would set up a copy row automation to copy the row to another sheet whenever the status changes to one of those two options. Finally you would use a COUNTIFS with cross sheet references to count how many times that unique identifier is on the other sheet.
-
Thanks so much. I thought my syntax was off with a simple formula; not that I had taken the wrong approach.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 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!