# Formula for Sum of distinct row and value

Options
✭✭✭✭

Hello,

I'm attempting to write a formula in the "total annual value of items per patient ID" that sums all the item value per distinct patient ID each time I enter a row. Anyone can help?

• ✭✭✭✭✭✭
edited 01/21/24
Options

Do you want something like this?

=IF(RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Patient ID]:[Patient ID], [Patient ID]@row, Year:Year, Year@row), 0) = 1, 1, 0)

Total Annual Value of Items per Patient ID:

=IF([Year Newest]@row, SUMIFS([Item Value]:[Item Value], [Patient ID]:[Patient ID], [Patient ID]@row, Year:Year, Year@row))

• ✭✭✭✭
Options

Is there a way to create the formula with the existing columns (without year newest?). We make a new sheet each year so there will never be more than 12 months on this grid sheet.

• ✭✭✭✭✭✭
Options

[Total with no helper columns]

=SUMIFS([Item Value]:[Item Value], [Patient ID]:[Patient ID], [Patient ID]@row)

The [Year Newest] column selects the newest item with a Patient ID of the same year. Without it, you will have a sum at all rows.

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!