Cross Reference Sheet with COUNTIF and DISTINCT Functions
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.
Best Answer
-
@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?
Answers
-
Try this: =COUNT(DISTINCT({FY Forecasts Vendor}))
-
@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")) -
@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?
-
@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. -
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?
-
@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!
-
@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?
-
YOU CRUSHED IT! I owe you a beer (or your preferred adult beverage)!!! Sorry I dragged that point on! Working perfectly!
-
Nice! Beer would be good.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!