Can someone explain on how this strange excel formula works exactly?

[jvs411]

So I wanted to find out how to sum up a range of numbers where only every subsequent number that is three columns apart will taken in to be calculated.
For example if I have the range of (2 , 3 , 5 , 1 , 2 , 4 , 3), the formula would only calculate 2+1+3 and return 6.

I asked an AI for help and it gave me this formula:

The formula DOES work in giving me 6 as shown but I'm curious as to how this formula works exactly?
From what I see, if the IF() statement is made true, wouldn't it only produce F9:L9?
And this means the formula will be SUMPRODUCT(F9:L9), which obviously won't give a result of 6, as seen below:

So how come the full formula in the first image works in giving the answer 6?? How does the formula work exactly?

 

[awoohaw]

Here is a step by step of the formula. I hope it works out.
SUMPRODUCT is essentially matrix arithmetic (also shown below the SUMPRODUCT line):

Mr Excel Questions 73.xlsm
	E	F	G	H	I	J	K	L
9		2	3	5	1	2	4	3
19		6
20	Column F9:L9	6	7	8	9	10	11	12
21	COLUMN(F9)	6	6	6	6	6	6	6
22	Difference	0	1	2	3	4	5	6
23	Modulo 3 of Difference	0	1	2	0	1	2	0
24	if equal to zero	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE
25	(value of TRUE or FALSE(unary operation))	1	0	0	1	0	0	1
26	Values in the range	2	3	5	1	2	4	3
27	SUMPRODUCT	6
28	Addl info: Matrix Mult	2	0	0	1	0	0	3
29	SUM of Matrix Mult	6
Sheet6

Cell Formulas
Range	Formula
F21:L21	G21	=COLUMN($F$9)
F22:L22	G22	=G20-G21
F19	F19	=SUMPRODUCT(IF(MOD(COLUMN(F9:L9)-COLUMN(F9),3)=0,F9:L9,0))
F20:L20	F20	=COLUMN(F9:L9)
F23:L23	F23	=MOD(F22:L22,3)
F24:L24	F24	=F23#=0
F25:L25	F25	=--(F24:L24)
F26:L26	F26	=F9:L9
F27	F27	=SUMPRODUCT(F25#,F26#)
F28:L28	F28	=F25#*F26#
F29	F29	=SUM(F28#)
Dynamic array formulas.

 

[Peter_SSs]

So I wanted to find out how to sum up a range of numbers where only every subsequent number that is three columns apart will taken in to be calculated.

I know your question in this thread is about the specific SUMPRODUCT formula, and I don't what your particular circumstances are but when trying to calculate from evenly spaced columns like that it is common for the headings in those columns to be the same or have something in common and those headings can be used to make the calculation easier. A couple of examples are shown below.

23 11 30.xlsm
	F	G	H	I	J	K	L	M	N	O	P
1	Budget	Actual	Total	Budget	Actual	Total	Budget	Actual	Total		Total Budget
2	2	3	5	1	2	3	3	5	8		6
3	6	4	10	4	4	8	7	1	9		17
4
5
6	Budget Jan	Actual Jan	Total Jan	Budget Feb	Actual Feb	Total Feb	Budget Mar	Actual Mar	Total Mar		Total Budget
7	2	3	5	1	2	3	3	5	8		6
8	6	4	10	4	4	8	7	1	9		17
Sample

Cell Formulas
Range	Formula
P2:P3	P2	=SUMIF(F$1:N$1,"Budget",F2:N2)
P7:P8	P7	=SUMIF(F$6:N$6,"Budget*",F7:N7)

Also, since you have Excel 365, here is another alternative if headings are not available or no use.

23 11 30.xlsm
	F	G	H	I	J	K	L
9	2	3	5	1	2	3	3
10
11	6
Sample (2)

Cell Formulas
Range	Formula
F11	F11	=SUM(INDEX(F9:L9,SEQUENCE(,ROUNDUP(COLUMNS(F9:L9)/3,0),,3)))

 

[jvs411]

Here is a step by step of the formula. I hope it works out.
SUMPRODUCT is essentially matrix arithmetic (also shown below the SUMPRODUCT line):

Mr Excel Questions 73.xlsm
	E	F	G	H	I	J	K	L
9		2	3	5	1	2	4	3
19		6
20	Column F9:L9	6	7	8	9	10	11	12
21	COLUMN(F9)	6	6	6	6	6	6	6
22	Difference	0	1	2	3	4	5	6
23	Modulo 3 of Difference	0	1	2	0	1	2	0
24	if equal to zero	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE
25	(value of TRUE or FALSE(unary operation))	1	0	0	1	0	0	1
26	Values in the range	2	3	5	1	2	4	3
27	SUMPRODUCT	6
28	Addl info: Matrix Mult	2	0	0	1	0	0	3
29	SUM of Matrix Mult	6
Sheet6

Cell Formulas
Range	Formula
F21:L21	G21	=COLUMN($F$9)
F22:L22	G22	=G20-G21
F19	F19	=SUMPRODUCT(IF(MOD(COLUMN(F9:L9)-COLUMN(F9),3)=0,F9:L9,0))
F20:L20	F20	=COLUMN(F9:L9)
F23:L23	F23	=MOD(F22:L22,3)
F24:L24	F24	=F23#=0
F25:L25	F25	=--(F24:L24)
F26:L26	F26	=F9:L9
F27	F27	=SUMPRODUCT(F25#,F26#)
F28:L28	F28	=F25#*F26#
F29	F29	=SUM(F28#)
Dynamic array formulas.

Thanks! That explains it perfectly