I need help with a SUM(DISTINCT(COLLECT formula

Radial_Rob

I am trying to sum the amount of opening in the Openings column for distinct REQs if they have an approved date of January. I keep getting the #invalid operation formula error.

Here is my formula

=SUM(DISTINCT(COLLECT({Openings}, {REQ}, {Date Approved}, MONTH(@cell) = 1)))

Please help

jmyzk_cloudsmart_jp

Hi @Radial_Rob

Understanding Your Question

It looks like your sheet contains duplicate entries, similar to the example in the image below.

For instance, if REQ = 1 has three entries with an Openings value of 3, you only want to count 3 once when summing for REQ = 1.

https://app.smartsheet.com/b/publish?EQBCT=f7f26df85d7d4f03bf8c9859f912c021

Solution Using Helper Columns

To ensure that only distinct Openings are summed for each REQ per month, I added two helper columns:

1. Row ID – A unique numeric identifier for each row.
   • Since we use the MIN function, Row ID must be a number (e.g., an auto-generated row number).
   • If you don’t have a numeric Row ID, you can dynamically use the MATCH function to determine the row number.
2. Distinct – Determines whether the entry is the first occurrence of a REQ in a given month.

The formula for the "Distinct" Column

=OR(
MIN(COLLECT([Row ID]:[Row ID], REQ:REQ, REQ@row, [Date Approved]:[Date Approved], MONTH(@cell) = MONTH([Date Approved]@row))) = [Row ID]@row,
MIN(COLLECT([Row ID]:[Row ID], REQ:REQ, REQ@row, [Date Approved]:[Date Approved], MONTH(@cell) = MONTH([Date Approved]@row))) = 0
)

Explanation of the Formula

• COLLECT([Row ID]:[Row ID], REQ:REQ, REQ@row, [Date Approved]:[Date Approved], MONTH(@cell) = MONTH([Date Approved]@row))
  • This function collects all Row ID values where:
    • REQ matches the current row’s REQ
    • The Date Approved is in the same month as the current row
• MIN(...)
  • Finds the smallest Row ID among the collected values (i.e., the first occurrence of the REQ in that month).
• MIN(...) = [Row ID]@row
  • Checks if the current row’s Row ID is the first entry for that REQ in the given month.
• The OR(... , MIN(...) = 0) part
  • Handles cases where there are no previous occurrences.

Summing Distinct Openings

Once the Distinct column is set up, you can sum only the rows where Distinct = TRUE to get the correct total for unique REQs by month.

The image below illustrates how this approach correctly sums the values.

https://app.smartsheet.com/b/publish?EQBCT=9d73d5b8edab4a538b2934a15c069fe1