Countifs Date range clash formula with Distinct value criteria

Sam Swain · 07/02/24

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

Mark Rojas · 07/02/24

@Sam Swain

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

Mark Rojas · 07/02/24

@Sam Swain

These also may be a good reference if my solution doesn't match your use case.

https://community.smartsheet.com/discussion/59196/formula-to-count-of-items-in-a-multi-dropdown-list

https://community.smartsheet.com/discussion/74511/how-to-count-multiple-drop-down-values

~MR

Sam Swain · 07/02/24

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?

Mark Rojas · 07/02/24

@Sam Swain

(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

Sam Swain · 07/03/24

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

Sam Swain · 07/03/24

Okay I think I have the answer, it's

=COUNTIFS(Start:Start, Start@row >= @cell, Finish:Finish, Finish@row <= @cell, 👤:👤, HAS(@cell, 👤@row))

Countifs Date range clash formula with Distinct value criteria

Answers

Help Article Resources

Categories