Cross Reference Sheet with COUNTIF and DISTINCT Functions

Jason Voorhies

Hi everyone,

I need help with a COUNTIF and DISTINCT function that I'm attempting to create in a summary sheet. I need the resulting count from the other sheet to exclude duplicates, so I settled on the following where XXX represents a vendor name. Not surprisingly I keep getting a Incorrect Argument in return. Open to any thoughts or suggestions.

Adam Murphy

@Jason Voorhies I think I understand what you want now!

Try this: =COUNT(DISTINCT(COLLECT({Assigned}, {FY Forecasts Vendor}, "XXX"))) where {Assigned} would be the field with your name. Does that work?

Adam Murphy

Try this: =COUNT(DISTINCT({FY Forecasts Vendor}))

Jason Voorhies

@Adam Murphy small step forward (thank you!) that eliminated the Incorrect Argument, but the count returned is incorrect. I also slightly modified it to include the value I want counted. Returned a count of 7 when I'm expecting 3.

=COUNT(DISTINCT({FY Forecasts Vendor}, "XXX"))

Adam Murphy

@Jason Voorhies sorry, I thought you wanted the count of distinct entries in a column (what I gave you), but it seems you want to match to a specific value (partner name). I think I am confused how the answer would ever be more than 1. How would the answer be 3? Would that mean that partner name appears in that column 3 times? If so, how are they distinct?

Are you just asking for a count where the partner name is "xxx"? =COUNTIF({FY Forecasts Vendor}, "XXX") Is that what you mean?

Jason Voorhies

@Adam Murphy please don't apologize as I didn't provide the necessary information to be successful on the first go around. Hopefully I can explain it more clearly. I have individuals that are tied to specific vendors, but those individuals can have different allocations towards specific projects, so they have a unique IDs to represent those allocations. In this instance it is Position Number. Hopefully this example helps:

Position Number 1 - Jason Voorhies - XXX (representative of vendor name)

Position Number 2 - Jason Voorhies - XXX (representative of vendor name)

Position Number 3 - Jason Voorhies - XXX (representative of vendor name)

I'm trying to avoid counting the Vendor 3 times in this example and for some reason I can't noodle it through with the cross sheet reference. I can get it to count 3 times, but really it should return one.

Adam Murphy

In your example, those are 3 separate columns, correct? Assuming the Partner Name (XXX) is by itself, why would the formula I gave you earlier not work? That should return a value of 1.

Another approach if you always want it to return a value of 1 for each partner would be something like: =IF(COUNTIF({FY Forecasts Vendor}, "XXX") > 0, 1, 0) That basically says to count how many times "XXX" appears in the column, and if it is more than 0, make the value 1, otherwise make it 0 (since it was not greater than 0). Does that work?

Jason Voorhies

@Adam Murphy again, I did you a disservice. I'm seeking the distinct count of individuals tied to that specific partner. My example still holds, but there could be Position Number 4 - Adam Murphy - XXX (same vendor). I'd want to know there are 2 people tied to that partner, not 4. If that doesn't make sense it is on me and I'll drop it. Apologies!

Adam Murphy

@Jason Voorhies I think I understand what you want now!

Try this: =COUNT(DISTINCT(COLLECT({Assigned}, {FY Forecasts Vendor}, "XXX"))) where {Assigned} would be the field with your name. Does that work?

Jason Voorhies

YOU CRUSHED IT! I owe you a beer (or your preferred adult beverage)!!! Sorry I dragged that point on! Working perfectly!

Adam Murphy

Nice! Beer would be good.