Correction to Sum of repeating values question

[jkharms]

Is there a formula that will sum the product of quantities in Column A times Column B
when both sets of numbers are separated by an equal and repeating number of rows?

A B
1
2
3 20
4
5 12...product of A3 x B5
6 15
7 plus
8 9 ... product of A6 x B8
9 5
10 plus
11 8 ...product of A9 x B11
12
...continuing on for an unlimited number of rows

 

[Aladin Akyurek]

You did propose both of those formula. The first (=SUMPRODUCT...) kept returning an error message, but the second (=SUM(IF...) eventually worked, but only if I added wavy brackets. Without those, it got the #VALUE error message. I appreciate your help with this. If I may, I'd like to ask you another question: can you give me an explanation of what the various segments of the =SUM (IF(MOD... formula are doing so that I can understand better how the formula works.

Thank you.

I had a typo in:

{=SUM(IF(MOD(ROW(A3:A9)-ROW(A3),3)=0,A3:A9*B5:B11))}

but I don't understand your additional parens. Would you post what you now have?​

 

[jkharms]

The formula that appears to work is =SUM(IF(MOD(ROW(A3:A1000),3)=0,A3:A1000*B5:B1002))

 

[Eric W]

The formula that appears to work is =SUM(IF(MOD(ROW(A3:A1000),3)=0,A3:A1000*B5:B1002))

The ROW function generates an array containing the row numbers of the range: {3,4,5,6,7 ...}

The MOD function returns the remainders when you divide by 3: {0,1,2,0,1,2, ...}

The =0 part checks for the remainders which equal zero, which will be every third row: {TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,...}

The IF part checks for the TRUE values and returns either the product of 2 values, or a FALSE : {A3*B5,FALSE,FALSE, A6*B8,FALSE,FALSE...}

Finally, the SUM adds up the numeric values in the array, ignoring the FALSE values.

 

[Aladin Akyurek]

The formula that appears to work is =SUM(IF(MOD(ROW(A3:A1000),3)=0,A3:A1000*B5:B1002))

if you want this to be robust, change it to:

{=SUM(IF(MOD(ROW(A3:A1000)-ROW(A3),3)=0,A3:A1000*B5:B1002))}

the following should also work:

=SUMPRODUCT(—(MOD(ROW(A3:A1000)-ROW(A3),3)=0),A3:A1000,B5:B1002)

 

[jkharms]

if you want this to be robust, change it to:

{=SUM(IF(MOD(ROW(A3:A1000)-ROW(A3),3)=0,A3:A1000*B5:B1002))}

the following should also work:

=SUMPRODUCT(—(MOD(ROW(A3:A1000)-ROW(A3),3)=0),A3:A1000,B5:B1002)

Thank you. The alternative worked as well. I appreciate your follow up.

 

[Aladin Akyurek]

Thank you. The alternative worked as well. I appreciate your follow up.

You are welcome.