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.

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.

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.

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.