Formula for Sum of distinct row and value

Drea Mora

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?

jmyzk_cloudsmart_jp

@Drea Mora

[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

jmyzk_cloudsmart_jp

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))

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

Drea Mora

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.

jmyzk_cloudsmart_jp

@Drea Mora

[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