Cumulative risk score per day between dates

Hawkeye

I have a number of events, each with a start date and an end date. Each event has a risk score that is the same throughout the event duration.

I would like to be able to show the total risk score for each individual day of the year, as multiple events may contribute to a venue’s risk. Thank you in advance!

Hawkeye

@KPH & @Hollie205 ,

thank you both for taking the time to help. We have solved it!

Hollie205

If your sheet is set up to where an event is only listed once and has the start date, end date, and risk score in separate columns. You will need a sheet with each date of the year listed then you can create a cross reference to your risk score, start date, and end date and use the formula below to add all of the risk scores that fall on that day.

=SUMIFS({risk score},{start date},<=[date of year]@row, {end date},<=[date of year]@row)

Hawkeye

Thank you @Hollie205, I think that I'm nearly there.

I have got INVALID OPERATION from the formula above, so I've not been able to make it a column formula.

And how do I write it so I don't need to write 365 different formulae when I try to 'fill right'?

Hollie205

Try =SUMIFS($[Overall rating score]:$[overall rating score],$[Start Date]@row>=[Column2]$1,$[End Date]@row,<=[Column2]$1)

You should then be able to drag your formula down and across since you have it on the same sheet. You won't be able to make a column formula on the same sheet because you have to have your date reference for the day of the year.

Hawkeye

@Hollie205, again thank you, but its still not working:

I wonder whether it could be around date formatting, as both Start and End Dates are in Date format, but it is more challenging to do this the Date Columns? If so, not sure how to solve that.

KPH

https://community.smartsheet.com/discussion/comment/402705#Comment_402705

This is a great solution from @Hollie205 and will create a very neat and easily readable list. However, the end date must be equal to or after the date of year

=SUMIFS({risk score}, {start date}, <=[date of year]@row, {end date}, >=[date of year]@row)

Hollie's solution takes this

And gives you this

To spin it around and have the dates in separate columns rather than separate rows like this looks troublesome....

https://community.smartsheet.com/discussion/comment/402733#Comment_402733

Firstly, the formula in your example is missing a comma after [Start Date]@row.

But I don't think that will solve your problems. I believe you are right about the date formatting...

You have the start and end dates in date format, which lets you do lovely things like sum data that falls within the range. But you can't make Column2 Coulumn3 etc. date format as only row 1 has a date in. But if you have them as Text/Number the date you enter in row 1 is very hard to match up to the dates in the date columns. There probably is a way involving converting dates to numeric strings and back again but that could be overkill.

If you need to do it this way around, the answer could be to put the dates that you are using as the column headings in Column2, Column3, etc., into a date column somewhere else and referencing them here.

This is the formula in Column2 (where it shows "0")

=SUMIFS([Overall Rating Score]:[Overall Rating Score], [Start Date]:[Start Date], <=[Dates (hidden)]2, [End Date]:[End Date], >=[Dates (hidden)]2)

This is the formula in Column5 (where it shows "40")

=SUMIFS([Overall Rating Score]:[Overall Rating Score], [Start Date]:[Start Date], <=[Dates (hidden)]5, [End Date]:[End Date], >=[Dates (hidden)]5)

You'd need to change the "5" in the formula to "4" for Column 4, "3" for Column 3, etc.

☹️ You cannot just drag the formula across

☹️ The "dates" in row 1 are not used and could cause confusion - if you change these the date below will not change - which is risky

I don't like it. Are you setting it up this way so that you can have one row per venue? Would it be possible to have one column per venue and then all the dates can be in one column? 🤞

Hollie205

@Hawkeye It is definitely the date formatting. Working with Dates in formulas causes issues.

If the dates are not needed to go in separate columns but could go down in separate rows would be the easiest resolution as @KPH has shown in the above screenshot with the date of year as a column title and total risk as a column title. And your total risk formula could be set up as a column formula and not have to be manually entered for each row.

If you use the above solution on the same sheet the below formula will work as a column formula.

=SUMIFS([Overall Rating Score]:[Overall Rating Score], [Start Date]:[Start Date], <=[Dates of year]@row, [End Date]:[End Date], >=[Dates of year]@row)

Going across with the date formatting issue would either require alot of helper columns literally one for each date of the year or to manually change the formula from one cell to the other. You would not have to create a date column to reference you can just change from referencing a cell to changing the date in each formula.

=SUMIFS([Overall Rating Score]:[Overall Rating Score], [Start Date]:[Start Date], <=Date(2023,1,1), [End Date]:[End Date], >=Date(2023,1,1)

=SUMIFS([Overall Rating Score]:[Overall Rating Score], [Start Date]:[Start Date], <=Date(2023,1,2), [End Date]:[End Date], >=Date(2023,1,2)

then continue to change for each next date.

Hawkeye

@KPH & @Hollie205 ,

thank you both for taking the time to help. We have solved it!

KPH

Great news @Hawkeye !