Correction to Sum of repeating values question

J

jkharms

Board Regular
Joined
Apr 17, 2009
Messages
102
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
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
 
Upvote 0
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:

ABCD

<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]
 
Upvote 0
Eric W said:
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:

ABCD

<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]
Click to expand...

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?
 
Upvote 0
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.
 
Upvote 0
Control+Shift+Return works for me on the Mac. Make sure you click in the formula, not just on the cell before the keystroke.
 
Upvote 0
Eric W said:
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.
Click to expand...

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?
 
Upvote 0
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!
 
Upvote 0

Excel 2010
ABCD
1
256
3256
4
55
63
7
86
94
10
117
7a
Cell Formulas
RangeFormula
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Repeating rows
Replies
2
Views
194
sharshra
S
Cubist
  • Solved
Expand each row n number of times.
2
Replies
16
Views
645
Cubist
Cubist
F
  • Solved
Formula to find location of second row from the bottom containing specific text
Replies
5
Views
77
Peter_SSs
Peter_SSs
T
  • Solved
Repeat names in a list by number of times in a cell
Replies
3
Views
419
Fluff
Fluff
N
  • Solved
CF for summing a specific number of columns
Replies
6
Views
82
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top