Sum every nth column for x instances (to get a weekly YTD number)

[nickcar]

Hi, I'm trying to sum every 3rd column up to the current week in order to have a current year to date sum up to our current fiscal week (or any fiscal week chosen). Below is the current setup, any ideas with sumproduct, offset, mod probably make sense but I can't wrap my head around what the combo looks like.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

YTD as of

22

FW 01

FW 01-2

FW 01-3

FW 02

FW 02-2

FW 02-3

FW 03

FW 03-2

FW 03-3

FW 04

FW 04-2

FW 04-3

FW 05

FW 05-2

FW 05-3

FW 06

FW 06-2

FW 06-3

12/31/18 - 12/29/19

Jan

12/31

1/6

Jan

1/7

1/13

Jan

1/14

1/20

Jan

1/21

1/27

Feb

1/28

2/3

Feb

2/4

2/10

Plan

Actual

Plan

Forecast

Actual

Plan

Forecast

Actual

Plan

Forecast

Actual

Plan

Forecast

Actual

Plan

Forecast

Actual

Plan

Forecast

Actual

#VALUE!

1,746.00

1,035.50

2,037.00

3,375.50

2,037.00

627.50

2,037.00

2,553.75

2,932.39

13,796.00

4,126.25

6,026.00

<colgroup><col style="width: 60px"><col width="60"><col width="60"><col width="21"><col width="21"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

Ideally i'd type a week number into B2 and that would update both A4 and B4 to reflect Plan and Actual up to that point. As you can see to the right we have each fiscal week with Plan and Actual, so I'd only need to sum every 3rd column up to the (week number) of columns.

Thanks
Nick

 

[kweaver]

I can't tell from your illustration which column contains which information.
However, if you want to sum the "Actuals" (in green) values below them:

=SUMPRODUCT(("Actual" = F4:W4)*F5:W5)

Where in my sheet, the first indication of Plan/Forecast/Actual began in F4 (with Plan), etc.

 

[nickcar]

Thanks kweaver that makes good sense. How would I limit that sumproduct to only the first x number of instances? So only up to week 22 of the 52 fiscal weeks of the year?

 

[kweaver]

In your example you're showing 6 sets of 3 columns (Plan, Forecast, Actual -- repeat).
But I cannot tell from the display if that represents 6 months (Jan, Feb, etc.) or what...so that the 22nd week would be where?

 

[nickcar]

Good question, what I pasted in is only the first 6 weeks of a 52 week year, the rest of the weeks continue in similar columns to the right. Ideally i’d reference a week number and have a YTD sum through that week.

 

[kweaver]

I put the number of weeks (e.g., 3) in C2.
Then, in A5 (below "Plan") I have:

=SUMPRODUCT(("Plan"=INDIRECT("$F$4:"&(ADDRESS(4,5+3*$C$2))))*INDIRECT("$F$5:"&ADDRESS(5,5+3*$C$2)))

And in B5 (below "Actual") I have:

=SUMPRODUCT(("Actual"=INDIRECT("$F$4:"&(ADDRESS(4,5+3*$C$2))))*INDIRECT("$F$5:"&ADDRESS(5,5+3*$C$2)))

As I mentioned in a previous post, my blocks of 3 start in F column.

 

[kweaver]

I shifted everything to the left to match what I now figured out are your column references.

=SUMPRODUCT(("Plan"=INDIRECT("$E$4:"&(ADDRESS(4,4+3*$B$2))))*INDIRECT("$E$5:"&ADDRESS(5,4+3*$B$2)))

The above code now goes into A5 under "Plan"...and the same code would be under "Actual" in B5 but you need to change the "Plan" to "Actual"

Hope this helps.

Kevin

 

[nickcar]

Awesome, yeah, I get it and was playing with it as well. Is there a way to make the cell references in quotes to be dynamic, so that if i dragged that formula down it'd reference it's current row? I used the row() function to do that within the address() function...

 

[Nishant94]

Maybe something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	YTD as of	22		FW 01	FW 01-2	FW 01-3	FW 02	FW 02-2	FW 02-3	FW 03	FW 03-2	FW 03-3	FW 04	FW 04-2	FW 04-3	FW 05	FW 05-2	FW 05-3	FW 06	FW 06-2	FW 06-3
2	12/31/18 - 12/29/19			Jan	Dec-31	01-Jun	Jan	01-Jul	Jan-13	Jan	Jan-14	Jan-20	Jan	Jan-21	Jan-27	Feb	Jan-28	02-Mar	Feb	02-Apr	02-Oct
3	Plan	Actual		Plan	Forecast	Actual	Plan	Forecast	Actual	Plan	Forecast	Actual	Plan	Forecast	Actual	Plan	Forecast	Actual	Plan	Forecast	Actual
4				1,746.00		1,035.50	2,037.00		3,375.50	2,037.00		627.5	2,037.00		2,553.75	2,932.39		13,796.00	4,126.25		6,026.00
5
6		YTD Total	27414.25
Sheet1

Cell Formulas
Range	Formula
C6	=SUMPRODUCT(SUMIFS(D4:U4,D3:U3,"Actual",B1:S1,"FW "&TEXT(ROW(A1:INDEX(A:A,B1)),"00")))