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
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
These also may be a good reference if my solution doesn't match your use case.
Hi
The drop-down multi select is very nice and useful. I started to use it yesterday, but I need to use the formula =countif(range, criterion).
I want to count how many times a value is repeated in a range that has multi values. I use the formula countif and only count one time a repeated value when the cell have multiple values. I hope I explain well my point.
Luis from PR
~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))
