SOLVED

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

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 Replies
best response confirmed by Andy_Godfrey (New Contributor)
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.

 

S0166.png

That works beautifully, Thanks so much.

Andy