Formula for distinct or unique totals based on multiple criterias

Linda F

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 30-60, 60-90 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 30-60 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:

Kelly Moore

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

Kelly Moore

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

Linda F

Thank you Kelly. I was able to figure out the formula was correct.