Formula for distinct or unique totals based on multiple criterias
I am creating a table to collect total REPORT KEYS that are unique month to month for each dept, each division and with unknown total transactions in each report key (we don't need to know how many transactions in each REPORT KEY). The data set can include up to 100 REPORT KEYS with no set amount of transactions for each. The criteria also consist of grouping by 3060, 6090 and 90 days for each month. One more criteria is that it needs be separated by TRAVEL or Payment Card REPORT KEYS. Below is the formula I am using which gives me "1" for each total.
=COUNT(DISTINCT(COLLECT({P&D Unassigned or Pending Transactions Range 1}, [Dept Codes]@row, {P&D Unassigned or Pending Transactions Range 2}, "Unassigned 3060 days", {P&D Unassigned or Pending Transactions Range 4}, "Payment Card", {P&D Unassigned or Pending Transactions Range 3}, 1)))
Here is a sample of my table also the 614 and 571 represent transactions  I need report keys referenced from another Smartsheet that contains the data:
Best Answer

Hey @Linda F
The syntax of your formula seems correct. Not seeing the source data, it's hard to troubleshoot. To troubleshoot yourself, change the COUNT/Distinct/collect to a JOIN/Collect. This will allow you to see what data is being pulled.
Also, double check that all the textstrings you have in quotes are an exact match to what's in your data set.
Let me know
Kelly
Answers

Hey @Linda F
The syntax of your formula seems correct. Not seeing the source data, it's hard to troubleshoot. To troubleshoot yourself, change the COUNT/Distinct/collect to a JOIN/Collect. This will allow you to see what data is being pulled.
Also, double check that all the textstrings you have in quotes are an exact match to what's in your data set.
Let me know
Kelly

Thank you Kelly. I was able to figure out the formula was correct.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!