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

[Andy_Godfrey]

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

Etc....

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

Andy

[#2 · Accepted solution]

@Andy_Godfrey 

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:

=SUMIFS($C$2:$C$100,$A$2:$A$100,"<="&F2,$B$2:$B$100,">="&F2)

Adjust the ranges, then fill down.

 

[#3]

That works beautifully, Thanks so much.

Andy