Calculated field add value for each UNIQUE row

I have a dataset of 19,000 rows. 3 rows have id of 123. of those three rows, there is a column dep that has 1 for each. When I get a count of dep its three. but I need count of 1 because the id is the same. How would I do that?
My thought is that one of the three rows will have 1 and the other 2 would have 0. which would work, but I wouldn’t know how to do that. Thanks!

Hi @japeter89
would be

an option?

No, that gives me a number of distinct ids, which is 1. but some of the ids might have ‘dep’ equal to 0
id dep
1 1
1 1
2 0
3 0
so in this example, I would want the number 1 to return. if I got a unique count of id’s I would get three. and if I got a count of dep’s I would get 2. But im trying to get unique id’s and then add those deps. make sense?

You only want the outcome?
id dep new_field
A 1 1
B 0 0
C 0 0
Means count-distinct id where dep=1?

Or what about avgOver id? Do you have 0 and 1 for a ID?

Hi @japeter89
Could you solve your issue?

realizing that I could filter down by adding filters but not including them in the sheet ended up being my resolve.