Get dates and conditional sum of budget spreads

MF NEOM

Hi Community. I have a spread of budgets but I want to retrieve the start and finish dates of the spread AND I want to get the planned sum of the spread up to a given date. Here is a print screen of the simplified table:

The Start Date and Finish Date columns is supposed to have the formulas that read the start date and the finish date from the spreads in the columns from 31 Jan 2024 to 5 Feb 2024. I used a Sumproduct formula in Excel to do this but for the life of me I cant get the answer in Smartsheet. The orange cell is a user input that is supposed to feed the formula (in the "Sum up to Orange Date" column) to sum value from left up to the orange cell date.

The date columns from 31 Jan 2024 to 5 Feb 2024 have the properties of text and not date hence the use of a cheat row. However, I am not sure if I need to have this "cheat row".

In summary, I need 3 formulas:

Start Date

Finish Date

Sum up to Orange Date

KPH

Hi @MF NEOM

Your "cheat row" is not going to provide much assistance, sadly. As the row is not a date format (not possible in smartsheet) the values entered are not going to be treated as dates, so you can't do any math such as summing up to a date, as they are going to be treated as text and there is no way to know 2-Feb is after 31-Jan or before 1-Mar.

Is there any chance you could separate your data from the summary and also pivot your data?

• The first 4 columns would be in one sheet, where the three dates can be in date format columns.
• The other columns would be on a different sheet. The rows would be columns - One column with a row for each date and then a column per activity.

That way you can put all the dates into date format columns and then use formula on them. It should also be easier to set up, as you can drag the dates down, not make a new column and name the column and the cheat row each time.

Your new data sheet would look like this:

And the summary sheet would be:

In this format you would be able to enter cross sheet formula on the summary sheet to bring in data from the data sheet.

For example:

If you set up cross sheet references and if the cross sheet reference for the date column was named "Dates" and the Activity A column was named "Activity A", you could use these three formula:

1. =MIN(COLLECT({Dates}, {Activity A}, <>""))
2. =MAX(COLLECT({Dates}, {Activity A}, <>""))
3. =SUMIF({Dates}, <=[Sum up to Orange Date]$1, {Activity A})

The summary sheet would look like this:

You could need to change the cross sheet reference for each row in the summary sheet

1. =MIN(COLLECT({Dates}, {Activity B}, <>""))
2. =MAX(COLLECT({Dates}, {Activity B}, <>""))
3. =SUMIF({Dates}, <=[Sum up to Orange Date]$1, {Activity B})