Formula for Sum of distinct row and value
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?
Best Answer
-
[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
Answers
-
Hi @Drea Mora
Do you want something like this?
Year Newest:
=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))
-
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.
-
[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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!