Counting Distinct with multiple ranges
Hi All,
Thanks!
Answers
-
Could you please provide a bit more information? What is the rule to use Drawing # vs Quote #? What defines distinct?
One way to possibly resolve is by adding a column that concatenates the fields (=[Drawing Number]@row + "_" + [Drawing Revision]@row + "_" + [Quote Number]@row + "_" + [Quote Revision]@row)
Depending on your rules, you may need to add a concatenated column for Drawing and Quote. Either way, the string value will be unique and you can count apply a count formula!
Let me know if you have any questions!
Respectfully,
Jeff
-
=count(distinct(collect([site type]:[site type],[drawing number]:[drawing number],"DW0002",[Quote Revision]:[Quote Revision],"A"
will return all unique values of site type where the drawing number is DW0002 and the quote revision is A
-
Thanks for the responses! What I'm trying to do is write a formula that will tell me how many distinct requests there were by site type. For example, based on the data above, how many distinct "New Equipment" requests are on the list? A specific site may have multiple requests (drawings, quotes or both and revisions) but I want to count that site just once. Below is how the count works out for New Equipment based on the data above:
DW0001 A, QT0005 A = 1 count
DW0002 A, QT0006 A & DW0002 B & DW0002 C = 1 count
QT0003 A & QT0003 B = 1 count
DW0011 A = 1 count
DW0013 A, QT0019A = 1 count
QT0021 A = 1 count
Total count for New Equipment requests = 6
Thanks for your help!
-
The above example is the long hand of what I'm trying to write a formula to accomplish. Does anyone have any additional thoughts on how to write a formula to accomplish this? Thanks!
-
Helper column with an if statement with the result being the number you want associated with the values, then a sumif.
or if you don't want to use that you can use stacked countif statements with mulipliers.
Apologies, i missed that you had responded to this.
-
I'm struggling to see how a helper column or stacked countif statements will accomplish what I'm trying to do. Maybe example formulas would help me. I thought about a helper column where I join the drawing and quote numbers and then count the distinct in the helper column. That wouldn't cover scenarios where a request included just a quote or drawing and not both. The challenge is writing a formula that recognizes which drawing numbers and quote numbers are for the same same site and counting all of them just once.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!