# Summing Every 4th Row on a Condition

#### MntnrMark

##### New Member

I have a large spreadsheet with over two thousand rows. It also has over 75 columns, but all of them (after Column A) are a repeating series of A, B, C and D. I've simplified my dilemma with the above example.

I want to write a formula that says, "If the number "X" ("X" is a number between 0 and 150) appears in any of the rows in Column A, Excel sums the values in every other fourth column in that row. For example, since "1" appears in A2 and A4 above, I need to sum 1) B2, F2, J2, . . . plus B4, F4, J4 . . . and every other fourth column after J with a "1" in Column A for A; 2 ) C2, G2, K2, . . . plus C4, G4, K4, . . . and every other fourth column after K with a "1" in Column A for B; 3) D2, H2, L2, . . . and every other fourth column after L with a "1" in Column A for C; and 4) E2,I2,M2, . . . plus E4, I4, M4, . . . and every other fourth column after M with a "1" in Column A for D. I can put the four summations for A, B, C and D at the bottom of the spreadsheet for each number 1 through 50, which would look something like this:

In summary, referencing the uppermost snippet, B2383 would sum every row with a 1 in Column A across Columns B, F, J, . . etc.; C2387 would sum every row with a 5 in Column A across Columns D, H, L, etc. (If the spreadsheet stopped at Column M and Row 5 Cell 2383 would show a total of 11+7+20+0+1+1, or 40.

With 150 numbers in Column A, one IF statement would get outrageously long. I'm thinking this might be a VLOOKUP candidate, but I'm not well versed in using VLOOKUP. I can, however, copy a VLOOKUP formula if someone can provide it (or another function that does what I need to accomplish) based on the example above.

Thanks, in advance, for the assistance!

#### Attachments

• 1614289185613.png
6.3 KB · Views: 3
• 1614290881967.png
3.5 KB · Views: 3
• 1614291020995.png
5 KB · Views: 3

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Eric W

##### MrExcel MVP
Try:

Book1
ABCDEFGHIJKLM
1No.ABCDABCDABCD
21117257281420233117
32345698761234
41042613571313
54103050042101
6
7
8
9No.ABCD
10140424952
11213141516
1230000
1348135
1450000
1560000
Sheet12
Cell Formulas
RangeFormula
B10:E15B10=SUMPRODUCT(\$B\$2:\$M\$5*(\$A\$2:\$A\$5=\$A10)*(\$B\$1:\$M\$1=B\$9))

#### MntnrMark

##### New Member
Try:

Book1
ABCDEFGHIJKLM
1No.ABCDABCDABCD
21117257281420233117
32345698761234
41042613571313
54103050042101
6
7
8
9No.ABCD
10140424952
11213141516
1230000
1348135
1450000
1560000
Sheet12
Cell Formulas
RangeFormula
B10:E15B10=SUMPRODUCT(\$B\$2:\$M\$5*(\$A\$2:\$A\$5=\$A10)*(\$B\$1:\$M\$1=B\$9))
Works like a charm! THANK YOU!

#### Eric W

##### MrExcel MVP
Happy to help! Thanks for the update!

Replies
2
Views
91
Replies
1
Views
88
Replies
4
Views
126
Replies
9
Views
219
Replies
12
Views
133

1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

### 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.

### Which adblocker are you using?

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

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