SUMIFS, OFFSET, INDEX - Nightmare

[JimmyG.]

I am at a complete loss and have been trying to work this out for hours but no matter how much I look around forums I can't figure it out.

So, the basic idea is find out how many emails will need to be read on a given day.

Emails will be received everyday including weekends and bank holidays but they wont be read until the next working day.

Each day (number) gets the same number of emails each month regardless of what day (name) it is.

On a Monday you would have Mondays emails, plus Saturday and Sundays to read, but on Tuesday you would only have Tuesdays.

I need a formula that will look at the date and if it is a non working day show nothing, but if it is a working day look up the day number and pull back the volume (easy), but the challenge is for it to look at the previous day (or days) and if it was a non working day add in that days (number) volume, and the day before that, and if its a bank holiday weekend the day before that.

I've attached a basic set up of the data. Column E is where I'm trying to get the result - happy to have as many helper columns as needed. Column G is the manual additions I've done to show what the result should be I am looking for - I just cant work out dynamic offsetting with summing values together

Thank you for any help with this as its driving me nuts

[JimmyG.]

After a bit of thinking I came up with this

Please Login or Register  to view this content.

Which works to a degree, but will add numbers in for sundays but also doesn't work for the first 4 days of the year

[Tsjallie]

As far as I see this should work:

Please Login or Register  to view this content.

Should the formula also consider the holidays?

[shg]

Given

	F	G	H
1	Day #	Volume	Holidays
2	1	16	Fri 01-Jan-2016
3	2	3	Fri 25-Mar-2016
4	3	2	Mon 28-Mar-2016
5	4	4	Mon 02-May-2016
6	5	3	Mon 30-May-2016
7	6	2	Mon 29-Aug-2016
8	7	4	Mon 26-Dec-2016
9	8	5	Tue 27-Dec-2016
10	9	3
11	10	2
12	11	2
13	12	3
14	13	9
15	14	2
16	15	3
17	16	5
18	17	4
19	18	1
20	19	2
21	20	7
22	21	4
23	22	5
24	23	6
25	24	2
26	25	3
27	26	4
28	27	8
29	28	1
30	29	3
31	30	1
32	31	2

Then ...

	A	B	C	D	E
1	Date	Rec'd	Read	Backlog
2	1/1/2016	16	0	16	B2: =INDEX($G$2:$G$32, DAY(A2))
3	1/2/2016	3	0	19	C2: =IF(WORKDAY(A2-1, 1, Holidays) = A2, B2 + N(D1), 0)
4	1/3/2016	2	0	21	D2: =SUM(B2, -C2, D1)
5	1/4/2016	4	25	0
6	1/5/2016	3	3	0
7	1/6/2016	2	2	0
8	1/7/2016	4	4	0
9	1/8/2016	5	5	0
10	1/9/2016	3	0	3
11	1/10/2016	2	0	5
12	1/11/2016	2	7	0
13	1/12/2016	3	3	0
14	1/13/2016	9	9	0
15	1/14/2016	2	2	0
16	1/15/2016	3	3	0
17	1/16/2016	5	0	5
18	1/17/2016	4	0	9
19	1/18/2016	1	10	0
20	1/19/2016	2	2	0
21	1/20/2016	7	7	0
22	1/21/2016	4	4	0
23	1/22/2016	5	5	0
24	1/23/2016	6	0	6
25	1/24/2016	2	0	8
26	1/25/2016	3	11	0
27	1/26/2016	4	4	0
28	1/27/2016	8	8	0
29	1/28/2016	1	1	0
30	1/29/2016	3	3	0
31	1/30/2016	1	0	1
32	1/31/2016	2	0	3
33	2/1/2016	16	19	0
34	2/2/2016	3	3	0
35	2/3/2016	2	2	0
36	2/4/2016	4	4	0

[JimmyG.]

SHG - thank you so much this is great.

If you don't mind, can you try and explain the formula so I understand how it works.

With the index I would have usually done a match - but this seems to just know where the day number is without referencing it - is that right?
The second one I'm at a loss, how does it know what to add together - what does the N mean? Is it like Nth number?

Thank you for any explanation if you have the time

[JimmyG.]

As far as I see this should work:

Please Login or Register  to view this content.

Should the formula also consider the holidays?

I couldn't quite get this to work, SHG's works great

[JimmyG.]

As far as I see this should work:

Please Login or Register  to view this content.

Should the formula also consider the holidays?

I couldn't quite get this to work, SHG's works great

[shg]

You're welcome.