SOLVED

New Contributor

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

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

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

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.

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

That works beautifully, Thanks so much.

Andy