Countifs Date range clash formula with Distinct value criteria
Hi all,
=COUNTIFS(Start:Start, Start@row >= @cell, Finish:Finish, Finish@row <= @cell, 👤:👤, 👤@row)
It returns a 1 as there is only 1 instance, but I would like it to return 2 because LM appears twice on the same date. This column allows multiple people to be selected.
I was thinking maybe of combining a countM or distinct function with formula above, but I don't know how.
Any help much appreciated,
Many thanks,
Sam
Answers
-
Im not sure if this is exactly what you are after but this will return the count per person.
=COUNTIFS(Start:Start, @cell = Start@row, Finish:Finish, @cell = Finish@row, Person:Person, CONTAINS("LM", @cell))
The down side to this is you will need to have a column per person. I believe the reason your current formula isn't pulling in more than one is because it sees the multi select as a "whole". But using a CONTAINS or HAS will be able to drill down deeper into the selection. (Person = your icon in the picture)
~MR
-
-
Hi Mark,
That works, but…
I'd like the end of the formula to look for distinct values so that if it finds the same initials on the same date on another row, it will COUNT. i.e., not just for LM, but for any combination of initials.
Any ideas?
-
(This is how I currently have the test set up. And I believe it is counting each individual instance under the columns per the set criteria.)
So in your case you want to know if ME & LM are scheduled together on the same date as well as their unique instances per row?
~MR
-
Hi Mark that is correct, but not just for ME and LM, for all initials. If I could be certain that the list of initials I have would be the final list, your solution of the extra columns works perfectly.
The issue I face is that new initials will be entered over time and I want my formula to require no updating should a new person's initials be entered
-
Okay I think I have the answer, it's
=COUNTIFS(Start:Start, Start@row >= @cell, Finish:Finish, Finish@row <= @cell, 👤:👤, HAS(@cell, 👤@row))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!