If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to collapse a table, what Function should I use?
I have a table of data (numbers) which is 70 rows by 21 columns, which I
would like to summarize into a 5 x 3 table. So every 14 rows by 7 columns sums into a single cell on another worksheet. My original table would be like 1 2 3 ... 20 21 1 2 .... 69 70 and my summary table would be 01-07 08-14 15-21 01-14 A B C 15-28 D E F 29-42 G H I 43-56 J K L 57-70 M N O where A = sum of all numbers in the intersection of rows 1-14 and columns 1-7 where O = sum of all numbers in the intersection of rows 57-70 and columns 15-21 Of course, my references to rows and columns in my A and O definitions are my header row and header column, not Excel rows and columns. I started with =SUMIFS(Original!$B$2:$V$71,Original!$B$1:$V$1,"= "&Left(B$1,2),Original!$B$1:$V$1,"="&Right(B$1,2) ,Original!$A$2:$A$71,"="&Left($A2,2),Original!$A$ 2:$A$71,"="&Right($A2,2)) which didn't work, so I tried a SUMPRODUCT formula which I can't get to work either. Obviously, I could come up with a formula for each entry (A-O above), but can anyone help me figure out a formula where I can enter it for the A entry above and copy to O? -- TIA, Brad E. |
#2
|
|||
|
|||
Trying to collapse a table, what Function should I use?
I kept playing with it and got something to work. I am using a simple SUM
function with an OFFSET, and putting a height and width in the OFFSET function. =SUM(OFFSET(Original!$A$1,VALUE(LEFT($A2,2)),VALUE (LEFT(B$1,2)),VALUE(RIGHT($A2,2))-VALUE(LEFT($A2,2))+1,VALUE(RIGHT(B$1,2))-VALUE(LEFT(B$1,2))+1)) I will continue checking back to see if anyone can come up with anything different. Thanks for your time. -- Brad E. "Brad E." wrote: I have a table of data (numbers) which is 70 rows by 21 columns, which I would like to summarize into a 5 x 3 table. So every 14 rows by 7 columns sums into a single cell on another worksheet. My original table would be like 1 2 3 ... 20 21 1 2 ... 69 70 and my summary table would be 01-07 08-14 15-21 01-14 A B C 15-28 D E F 29-42 G H I 43-56 J K L 57-70 M N O where A = sum of all numbers in the intersection of rows 1-14 and columns 1-7 where O = sum of all numbers in the intersection of rows 57-70 and columns 15-21 Of course, my references to rows and columns in my A and O definitions are my header row and header column, not Excel rows and columns. I started with =SUMIFS(Original!$B$2:$V$71,Original!$B$1:$V$1,"= "&Left(B$1,2),Original!$B$1:$V$1,"="&Right(B$1,2) ,Original!$A$2:$A$71,"="&Left($A2,2),Original!$A$ 2:$A$71,"="&Right($A2,2)) which didn't work, so I tried a SUMPRODUCT formula which I can't get to work either. Obviously, I could come up with a formula for each entry (A-O above), but can anyone help me figure out a formula where I can enter it for the A entry above and copy to O? -- TIA, Brad E. |
#3
|
|||
|
|||
Trying to collapse a table, what Function should I use?
Is there a reason you're not using Excel's group/outline feature?
On Mar 19, 9:25*am, Brad E. wrote: I have a table of data (numbers) which is 70 rows by 21 columns, which I would like to summarize into a 5 x 3 table. *So every 14 rows by 7 columns sums into a single cell on another worksheet. My original table would be like * *1 *2 *3 ... 20 *21 1 2 ... 69 70 and my summary table would be * * * * *01-07 *08-14 *15-21 01-14 * * A * * * *B * * * *C 15-28 * * D * * * *E * * * *F 29-42 * * G * * * *H * * * *I 43-56 * * J * * * *K * * * *L 57-70 * * M * * * *N * * * *O where A = sum of all numbers in the intersection of rows 1-14 and columns 1-7 where O = sum of all numbers in the intersection of rows 57-70 and columns 15-21 Of course, my references to rows and columns in my A and O definitions are my header row and header column, not Excel rows and columns. I started with =SUMIFS(Original!$B$2:$V$71,Original!$B$1:$V$1,"= "&Left(B$1,2),Original!$B$1:$V$1,"="&Right(B$1,2) ,Original!$A$2:$A$71,"="&Left($A2,2),Original!$A$ 2:$A$71,"="&Right($A2,2)) which didn't work, so I tried a SUMPRODUCT formula which I can't get to work either. Obviously, I could come up with a formula for each entry (A-O above), but can anyone help me figure out a formula where I can enter it for the A entry above and copy to O? -- TIA, Brad E. |
#4
|
|||
|
|||
Trying to collapse a table, what Function should I use?
=SUM(OFFSET(Original!$B$2,(ROW(1:1)-1)*14,(COLUMN()-COLUMN($B:$B))*7,14,7))
if u dont insert formula in column B in destination sheet then change COLUMN($B:$B) to suit "Brad E." skrev: I have a table of data (numbers) which is 70 rows by 21 columns, which I would like to summarize into a 5 x 3 table. So every 14 rows by 7 columns sums into a single cell on another worksheet. My original table would be like 1 2 3 ... 20 21 1 2 ... 69 70 and my summary table would be 01-07 08-14 15-21 01-14 A B C 15-28 D E F 29-42 G H I 43-56 J K L 57-70 M N O where A = sum of all numbers in the intersection of rows 1-14 and columns 1-7 where O = sum of all numbers in the intersection of rows 57-70 and columns 15-21 Of course, my references to rows and columns in my A and O definitions are my header row and header column, not Excel rows and columns. I started with =SUMIFS(Original!$B$2:$V$71,Original!$B$1:$V$1,"= "&Left(B$1,2),Original!$B$1:$V$1,"="&Right(B$1,2) ,Original!$A$2:$A$71,"="&Left($A2,2),Original!$A$ 2:$A$71,"="&Right($A2,2)) which didn't work, so I tried a SUMPRODUCT formula which I can't get to work either. Obviously, I could come up with a formula for each entry (A-O above), but can anyone help me figure out a formula where I can enter it for the A entry above and copy to O? -- TIA, Brad E. |
Thread Tools | |
Display Modes | |
|
|