Adding up numbers that fall in a range of dates (not as simple as it sounds)

New Contributor

Hi All


I assume that this isn't that complicated and I am just lacking the correct terms to find the answers. I just get solutions to kind of the opposite problem when I try.


Here is my problem ...

We generate a spreadsheet with events (multiple days) and numbers of staff e.g.

Start date      End date      Number of staff working
1 Jan 21         3 Jan 21       5
2 Jan 21         2 Jan 21       3
5 Jan 21         10 Jan 21     10

What I would ideally like is to then have a second sheet with all the dates of the year in column A and then how many staff are working on that date so with the above it would look like
1 jan 21         5
2 jan 21         8
3 jan 21         5
4 jan 21
5 jan 21         10
6 Jan 21         10


Is that possible?


I'm not wedded to the other sheet idea I just thought that would be easiest

I hope I have posted this in the right place

Thanks so much


2 Replies
best response confirmed by Andy_Godfrey (New Contributor)


Let's say that you enter the dates 1-Jan-21, 2-Jan-21 etc. in F2 and down.

Enter the following formula in G2:




Adjust the ranges, then fill down.



That works beautifully, Thanks so much.