Summing Data based on a count of prior month data

[pdbose]

Hey Everyone!

Been following this site for a while now and finally posting something because I have spent the better part of all day today working on this and am complete stumped on how to solve for this one. Hoping someone can help! Screenshot below. I'm building out a forecasting model and I'm trying to come up with a formula that compares prior month sales to current sales by looking at the previous month's sales as a percentage of the total # of sales days for the current v previous month. In the data below. February has 23 business days and January has 19 business days. Since there are 2 business days of data for the current month, February (C2), the calculation should solve accordingly.

2 business days/23 business days (February) * 19 business days (January) = 1.65 days
So the formula should calculate $100 for the first business day in January, then 65% of the 2nd business day ($200) for a total of $230

Where things get tricky is this formula needs to adjust based on the selected month in C2 and find the correct columns in Row 11. It then needs to take the business days in Row 7 to run the calculation above, as well as go into an indirect function that allow me to lookup based off the "Remaining BD - xx" items in B12:B34. Essentially, this formula needs to sum everything within the range of "Remaining BD - 18" and Remaining BD - 17" for the January (since there's only 2 business days in February with populated values), while also applying the 65% calculation to the January value coinciding with "Remaining BD - 17". I'm able to create INDEX(MATCH()) functions and INDIRECT() functions that solve many of the problems individually but I'm unable to tie them all together without it breaking.

 

[KRice]

I've asked several times for more detail about expected results and haven't received any clarity on that. I don't understand how your monthly table is being used, or why is it structured with offsets at the top of the table (as opposed to adopting an idea I mentioned in post #2 about aligning all monthly entries such that the 1st business day of the month is in row 12 (for all months). If you keep the current structure with the daily offsets at the top for each month, and you want to sum only n entries in the prior month (assuming there are only n entries in the current month), I would use the following:

MrExcel_20240131_C (version 1).xlsx
	B	C
1	2024
2	Current Month	March
3	sum prior month	303
Forecast

Cell Formulas
Range	Formula
C3	C3	=LET(tbl,$C$12:$N$34,c,MONTH(C2),curmo,INDEX(tbl,,c),primo,INDEX(tbl,,c-1),numd,COUNTA(curmo),SUM(TAKE(FILTER(primo,primo<>""),numd)))

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$C$11#

...where the drop down list in C2 refers to the range of monthly column headings in your table. These "month" cells actually contain the full date (the 1st of the month for the year in B1), but they are formatted to display "mmmm", which shows only the name of the month.

MrExcel_20240131_C (version 1).xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
11	Business Work Day	January	February	March	April	May	June	July	August	September	October	November	December
12	Remaining BD - 22		100	4
13	Remaining BD - 21		200	2	88
14	Remaining BD - 20		3	8	66
15	Remaining BD - 19		1		66
16	Remaining BD - 18	100	5		3
17	Remaining BD - 17	200
18	Remaining BD - 16	300				22
19	Remaining BD - 15	2				33
20	Remaining BD - 14	1
21	Remaining BD - 13	8
22	Remaining BD - 12
Forecast

Cell Formulas
Range	Formula
C11:N11	C11	=DATE($B$1,SEQUENCE(,12),1)
B12:B34	B12	="Remaining BD - "&SEQUENCE(23,,22,-1)
Dynamic array formulas.

 

[pdbose]

Hi @KRice, thanks for the help with this. Referring back to my previous post, I believe I solved the issue. This is now running in my model is giving me the correct calculation for now.

I think I may have figure it out!

=SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX($C$12:$N$34,COUNTA(INDEX($C$12:$N$34,0,MATCH($C$2,$C$11:$N$11,0)))+(INDEX($C$7:$N$7,1,MATCH($C$2,$C$11:$N$11,0))-INDEX($C$7:$N$7,1,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))),MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)))


Then, in order to get the comparison to last month, I would use

=(INDEX($C$35:$N$35,1,MATCH($C$2,$C$11:$N$11,0))-SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX($C$12:$N$34,COUNTA(INDEX($C$12:$N$34,0,MATCH($C$2,$C$11:$N$11,0)))+(INDEX($C$7:$N$7,1,MATCH($C$2,$C$11:$N$11,0))-INDEX($C$7:$N$7,1,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))),MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))))/SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX($C$12:$N$34,COUNTA(INDEX($C$12:$N$34,0,MATCH($C$2,$C$11:$N$11,0)))+(INDEX($C$7:$N$7,1,MATCH($C$2,$C$11:$N$11,0))-INDEX($C$7:$N$7,1,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))),MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)))

From my other post, this is what we were solving for, circled in red.

 

[KRice]

I do not see how those formulas will deliver what you described. My understanding is that you want to do the following steps:

1. Pick a date (month) in C2.
2. Have the formula look at the sales data table (C11:N34, headings in 1st row) for that same month and count the number of sales entries for that selected month.
3. Return the sum for the same number of sales entries in the prior month, beginning with the first sales entry in the prior month.

Let's say you pick "March" in C2, and there are 3 sales entries for March as shown below. So the sum returned is the 1st three sales entries for February, which is 303. This is done with relatively simple formulas in C3 (red cell), or if you want to avoid using C2, obtain results for all months with a single spilling formula in C5 (yellow cells).

But I have yet to see a clear explanation about the expected results. For the example below, I've started each month's sales data in arbitrary locations rather than basing the first entry on the number of business days for the month.

MrExcel_20240131_C (version 1).xlsx
	B	C	D	E	F	G	H
1	2024
2	Current Month	March		100
3	sum prior month	303		-1
4
5		603	303	14	154	22	0
6
7	Business Days	19	23	21	22	22	20
8
9
10
11	Business Work Day	January	February	March	April	May	June
12	Remaining BD - 22		100	4
13	Remaining BD - 21		200	2	88
14	Remaining BD - 20		3	8	66		3
15	Remaining BD - 19		1		66
16	Remaining BD - 18	100	5		3
17	Remaining BD - 17	200
18	Remaining BD - 16	300				22
19	Remaining BD - 15	2				33
20	Remaining BD - 14	1
21	Remaining BD - 13	8
22	Remaining BD - 12
Forecast

Cell Formulas
Range	Formula
C3	C3	=LET(tbl,$C$12:$N$34,c,MONTH(C2),curmo,INDEX(tbl,,c),primo,INDEX(tbl,,c-1),numd,COUNTA(curmo),SUM(TAKE(FILTER(primo,primo<>""),numd)))
E2	E2	=SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX($C$12:$N$34,COUNTA(INDEX($C$12:$N$34,0,MATCH($C$2,$C$11:$N$11,0)))+(INDEX($C$7:$N$7,1,MATCH($C$2,$C$11:$N$11,0))-INDEX($C$7:$N$7,1,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))),MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)))
E3	E3	=(INDEX($C$35:$N$35,1,MATCH($C$2,$C$11:$N$11,0))-SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX($C$12:$N$34,COUNTA(INDEX($C$12:$N$34,0,MATCH($C$2,$C$11:$N$11,0)))+(INDEX($C$7:$N$7,1,MATCH($C$2,$C$11:$N$11,0))-INDEX($C$7:$N$7,1,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))),MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))))/SUM(INDEX($C$12:$N$12,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)):INDEX($C$12:$N$34,COUNTA(INDEX($C$12:$N$34,0,MATCH($C$2,$C$11:$N$11,0)))+(INDEX($C$7:$N$7,1,MATCH($C$2,$C$11:$N$11,0))-INDEX($C$7:$N$7,1,MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0))),MATCH(DATE(YEAR($C$2),MONTH($C$2)-1,1),$C$11:$N$11,0)))
C5:H5	C5	=IFERROR(SUM(TAKE(FILTER(C$12:C$34,C$12:C$34<>""),COUNTA(D$12:D$34))),0)
C7,F7:H7	C7	=NETWORKDAYS.INTL(C11,EOMONTH(C11,0),1,$Q$2:$Q$15)
C11:N11	C11	=DATE($B$1,SEQUENCE(,12),1)
B12:B34	B12	="Remaining BD - "&SEQUENCE(23,,22,-1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$C$11#