Avoid duplicates with a countifs formula

Mike Ro

I'm trying to avoid duplicates with a countifs formula.

I'm looking to count pending, future, or approved leave types, but I want toavoid counting the same employee ID twice.

Below is my formula that I have been using; however, I keep getting an unparsable error message

Any help would be appreciated.

=COUNTIFS(DISTINCT({EE ID},{LOA Tracker Range 1})> 1, 1{Leave Type}, [Primary Column]@row, {Status Pending}, "future")

Jgorsich

Your formula has quite a few bugs in it. Was it originally written by the smartsheet AI? I've found that to be unreliable - it will occasionally invent capabilities that don't exist and mangle some formulas pretty hard.

For reference, here are the instructions for distinct():

DISTINCT Function | Smartsheet Learning Center

And here are the instructions for countifs():

COUNTIFS Function | Smartsheet Learning Center

What you'd likely want is a count(distinct(collect())) type situation, where first you collect all the employee IDs that meet your criteria, then you reduce them to distinct members only, then you count them. You could also just reduce this to a simple countifs() if you add a helper column that allows you to identify if the entry new.

I'll give the formula next, but note - if you do it this way and you have an employee that has entered requests for two leaves of the same type but on different days, it will only count it as 1 instead of 2. Make sure this is what you want.

The following is based on trying to parse your original function - you've got some very odd placement of commas in there, so I'm not sure I'm 100% correct.

{EE ID} - this is your column of employee IDs and what you want to count

{LOA Tracker Range 1}) - what ever this is you want the value to be > 1

{Leave Type} - this appears to define what type of leave you are looking at, with the values you want in [Primary Column]@row

{Status Pending} - you want this to be equal to "future"

=count(distinct(collect({EE ID},{LOA Tracker Range 1},>1, {Leave Type}, [Primary Column]@row, {Status Pending}, "future")))

I believe that will work for you if my assumptions are correct, though I've not tested it.