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:
