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

 

[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

 

[Eric W]

Your question isn't entirely clear. Next time try using the HTML Maker from my signature which formats your sample sheet better. Nevertheless, this might work for you:

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]56[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=SUM(IF(MOD(ROW(A3:A100),3)=0,A3:A100*B5:B102))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[jkharms]

Your question isn't entirely clear. Next time try using the HTML Maker from my signature which formats your sample sheet better. Nevertheless, this might work for you:

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]56[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]{=SUM(IF(MOD(ROW(A3:A100),3)=0,A3:A100*B5:B102))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your help.
I typed in the formula you provided. Then pressed Ctrl + Shift + Enter, but did not get the {} brackets. (I'm using a MAC, which may be the problem...or I'm doing the entering wrong.)
I get the formula to go into the target cell, but I get #VALUE in the cell.
Any thoughts?

 

[Eric W]

I'm much less use to you since I don't have a Mac. However, on older versions of Excel for Mac, the keystroke combination for an array formula was (4-leaf clover button) + return. I believe on newer versions they switched it to CSE like on Windows PCs. Give that a shot, if that doesn't work, then maybe someone with a Mac might chime in.

 

[KevCarter]

Control+Shift+Return works for me on the Mac. Make sure you click in the formula, not just on the cell before the keystroke.

 

[jkharms]

I'm much less use to you since I don't have a Mac. However, on older versions of Excel for Mac, the keystroke combination for an array formula was (4-leaf clover button) + return. I believe on newer versions they switched it to CSE like on Windows PCs. Give that a shot, if that doesn't work, then maybe someone with a Mac might chime in.

Thanks again, Eric,
I moved over to a PC and managed to get your formula, with the wavy brackets, inserted. However, now the word in the cell changed from #VALUE to #N/A. I've reviewed the formula I inserted several times, and it appears to be exactly what you suggested. I also filled in numbers in the proper rows and columns (Rows 3,6,9,12 - Columns 5,8,11,14) to check the calculations. Any thoughts on what I could be doing wrong?

 

[jkharms]

Control+Shift+Return works for me on the Mac. Make sure you click in the formula, not just on the cell before the keystroke.

Thank you. I'll make sure to be clicking in the formula. Appreciate your help.

 

[KevCarter]

It sounds like you are still having issues with the formula. I wish I was more knowledgeable and could help. I’m sure the others will get you onto a solution. Good luck JK!

 

[Dave Patton]

Excel 2010
	A	B	C	D
1
2				56
3	2			56
4
5		5
6	3
7
8		6
9	4
10
11		7
7a

Cell Formulas
Range	Formula
D3	=SUMPRODUCT(--(MOD(ROW(A3:A100),3)=0),A3:A100,B5:B102)
D2	{=SUM(IF(MOD(ROW(A3:A100),3)=0,A3:A100*B5:B102))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Your question indicated the numbers in Columns A and B.

Your statement that follows is not consistent with the initial question.

"I also filled in numbers in the proper rows and columns (Rows 3,6,9,12 - Columns 5,8,11,14) to check the calculations. Any thoughts on what I could be doing wrong?


Try submitting a concise example that reflects your challenge.

Is this question duplicated at

https://www.mrexcel.com/forum/excel-questions/1044560-sum-repeating-values.html#post5014950