Cumulative risk score per day between dates

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)

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.

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

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? 🤞