Summing Every 4th Row on a Condition

MntnrMark

New Member
Joined
Dec 18, 2012
Messages
40
1614289360706.png


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:

1614291287444.png


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
    1614289185613.png
    6.3 KB · Views: 3
  • 1614290881967.png
    1614290881967.png
    3.5 KB · Views: 3
  • 1614291020995.png
    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
Joined
Aug 18, 2015
Messages
10,807
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))
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top