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

 

[Eric W]

I'm not sure what's going on. I would have expected a problem if something in that range is alphabetic, but that would generate a #VALUE error. Can you show me what data you're using?

When you mentioned (Rows 3,6,9,12 - Columns 5,8,11,14) did you really mean A3,A6,A9,A12 and B5,B8,B11,B14, or are you looking a multiple columns (E,H,K,N), in which case I don't understand your requirements?

 

[Fluff]

@jkharms
Please do not duplicate your threads.
Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.

I have merged both threads

 

[jkharms]

Excel 2010

	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"]56[/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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

7a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet 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] "]D3[/TH]
[TD="align: left"]=SUMPRODUCT(--(MOD(ROW(A3:A100),3)=0),A3:A100,B5:B102)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[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]




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

I'll try again.
There are two sets of values. One set, beginning in row 3, are each consistently 3 rows apart (a value in row 3, 6, 9, 12, etc.). There are also values appearing in Column B, beginning in row 4, that are also consistently 3 rows apart, (a value in row 4, 7, 11, 14, etc.). I'm looking for a formula to place in C2 that will sum the product of the values in A3 x B4, A6 x B7, etc.

 

[jkharms]

Sorry to have done this incorrectly. I thought I had made a mistake in the way my original thread was formatted, making it hard for others to understand properly. I was attempting to correct that with a second quickly following thread (that's the reason the second carries the terminology "correction". Being an infrequent user, I sometimes struggle stating my issues as clearly as I'd like. I appreciate your concern.

 

[Dave Patton]

Excel 2010
	A	B	C	D
1
2			86	86
3	2
4		5
5
6	3
7		6
8
9	4
10		7
11
12	5
13		6
7aa

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

 

[jkharms]

I'm not sure what's going on. I would have expected a problem if something in that range is alphabetic, but that would generate a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] error. Can you show me what data you're using?

When you mentioned (Rows 3,6,9,12 - Columns 5,8,11,14) did you really mean A3,A6,A9,A12 and B5,B8,B11,B14, or are you looking a multiple columns (E,H,K,N), in which case I don't understand your requirements?

I do mean A3, A6, A9, A12, etc...same arrangement with column B.

For note: Before adding the wavy brackets { } to the formula, I did get the #VALUE error message. After adding the { } I got the #N/A message.

There are only numbers in the various rows...numbers I inserted to allow checking the accuracy of the formula.

Really appreciate your looking into this.

 

[Eric W]

I'm afraid I'm at a loss here. I can't see why it's working for me and not for you. Try posting your formula exactly as you entered it, including the exact ranges you're using. Show us what data you are including in those ranges, including the numbers, empty cells, formulas in the cells that return numbers or text or "".

 

[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]

I reentered this formula, and it worked this time...so thank you very much for the help. If you wouldn't mind, I would really appreciate an explanation of the process the formula is describing. And also, of what significance in the formula is the =0 designation?
Thanks again for your help.

 

[Aladin Akyurek]

Did't I already propose in your original thread such a solution?

=SUMPRODUCT(--(MOD(ROW(A3:A9)-ROW(A3),3)=0),A3:A9,B5:B11)

Or, if you wish, control+shift+enter, not just enter:

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

 

[jkharms]

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.