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!
