Tricky formula!

Christina Reid

I receive a report each month that gives me values at the end of each month for a particular group of individuals. See below for the report. The report is delayed so I'll get new numbers for November in December, etc.

I need to first report on AUA #'s for that month and then YTD.

For example, I'm running numbers for March of 2024. I don't want Jane Doe to be considered as her Post Affiliation End Date is January of 2024, Joe Smith should have his March 2024 numbers considered and Person 1 should have their March 2024 numbers considered.

But for YTD that gets trickier because I can't simply add up the month-to-month numbers as it would overreport (AUA is just the total AUA that month, not net new AUA. So for YTD I need it to use the last month's AUA that should have been reported on for Jane Doe (which would have been January 2024) plus Joe Smith's March 2024 total and Person 1's March 2024 total).

Then when I work on April 2024 it would only consider Person 1's total as Joe Smith ended in March of 2024. But for the YTD it needs to calculate Jane Doe's January 2024 total, Joe Smith's March 2024 total and the April 2024 for Person 1.

New Advisors will get added throughout the year, so the calculations need to take into consideration the existing and new people that might get added.

I don't mind have the totals calculate on a different sheet, and it'd honestly be easier so that each month I get a new report I can just paste that into the "master sheet" and still have the calculations work.

If you can help solve this - you will have my undying gratitude! Otherwise, I have to manually grab and add numbers….🤦‍♀️

jmyzk_cloudsmart_jp

Hi @Christina Reid

Here is a demo solution.
In the solution, I use the Sheet Summary fields to designate Year and Month and show the AUA # for the month and YTD.

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

First, I get the [affilliated] advisors by the following formula;

=JOIN(COLLECT(Advisor:Advisor, [Post Affiliation End Date]:[Post Affiliation End Date], ISDATE(@cell ), [Post Affiliation End Date]:[Post Affiliation End Date], MONTH(@cell ) >= Month#), CHAR(10))@cell

Then, using the value, I get the AUA and YTD with the following formulas;

=SUMIFS(AUA:AUA, Advisor:Advisor, CONTAINS(@cell, Affiliated#), [Month(Date)]:[Month(Date)], MONTH(@cell ) = Month#)

=SUMIFS(AUA:AUA, [Month(Date)]:[Month(Date)], AND(YEAR(@cell ) = Year#, MONTH(@cell ) >= 1, MONTH(@cell ) <= Month#))

Explanation of the Formula

1. =JOIN(COLLECT(Advisor:Advisor, [Post Affiliation End Date]:[Post Affiliation End Date], ISDATE(@cell), [Post Affiliation End Date]:[Post Affiliation End Date], MONTH(@cell) >= Month#), CHAR(10))
   • Key Points:
     • Filters rows where the Post Affiliation End Date exists (ISDATE(@cell)).
     • Ensures the advisor’s Post Affiliation End Date is on or after the specified month (MONTH(@cell) >= Month#).
     • Joins the matching advisor names into a single list.
2. =SUMIFS(AUA:AUA, Advisor:Advisor, CONTAINS(@cell, Affiliated#), [Month(Date)]:[Month(Date)], MONTH(@cell) = Month#)
   • Key Points:
     • Matches AUA values for affiliated advisors (CONTAINS(@cell, Affiliated#)).
     • Filters the data for the specified month (MONTH(@cell) = Month#).
3. =SUMIFS(AUA:AUA, [Month(Date)]:[Month(Date)], AND(YEAR(@cell) = Year#, MONTH(@cell) >= 1, MONTH(@cell) <= Month#))
   • Key Points:
     • Includes data from January to the specified month (MONTH(@cell) >= 1 and MONTH(@cell) <= Month#).
     • Restricts the calculation to the relevant year (YEAR(@cell) = Year#).

This approach leverages Smartsheet’s COLLECT, JOIN, and SUMIFS functions to efficiently calculate monthly and year-to-date AUA, dynamically adjusting for the specified timeframe and affiliated advisors.