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.
0
Utf
3/19/2010 1:25:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1148 Views

Similar Articles

[PageSpeed] 8

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.
0
Utf
3/19/2010 2:50:01 PM
Is there a reason you're not using Excel's group/outline feature?

On Mar 19, 9:25=A0am, Brad E. <Br...@discussions.microsoft.com> 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. =A0So every 14 rows by 7 colu=
mns
> sums into a single cell on another worksheet.
> My original table would be like
> =A0 =A01 =A02 =A03 ... 20 =A021
> 1
> 2
> ...
> 69
> 70
>
> and my summary table would be
> =A0 =A0 =A0 =A0 =A001-07 =A008-14 =A015-21
> 01-14 =A0 =A0 A =A0 =A0 =A0 =A0B =A0 =A0 =A0 =A0C
> 15-28 =A0 =A0 D =A0 =A0 =A0 =A0E =A0 =A0 =A0 =A0F
> 29-42 =A0 =A0 G =A0 =A0 =A0 =A0H =A0 =A0 =A0 =A0I
> 43-56 =A0 =A0 J =A0 =A0 =A0 =A0K =A0 =A0 =A0 =A0L
> 57-70 =A0 =A0 M =A0 =A0 =A0 =A0N =A0 =A0 =A0 =A0O
> where A =3D sum of all numbers in the intersection of rows 1-14 and colum=
ns 1-7
> where O =3D sum of all numbers in the intersection of rows 57-70 and colu=
mns
> 15-21
> Of course, my references to rows and columns in my A and O definitions ar=
e
> my header row and header column, not Excel rows and columns.
>
> I started with
> =3DSUMIFS(Original!$B$2:$V$71,Original!$B$1:$V$1,">=3D"&Left(B$1,2),Origi=
nal!$B$1:$V$1,"<=3D"&Right(B$1,2),Original!$A$2:$A$71,">=3D"&Left($A2,2),Or=
iginal!$A$2:$A$71,"<=3D"&Right($A2,2))
> which didn't work, so I tried a SUMPRODUCT formula which I can't get to w=
ork
> 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 en=
try
> above and copy to O?
> -- TIA, Brad E.

0
Amish
3/19/2010 4:17:40 PM
=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.
0
Utf
3/20/2010 9:10:01 AM
Reply:

Similar Artilces:

How to use outlook address in Excel
Hello, I have an Excel sheet which I use as an invoicing-application. I would like to retrieve address-data from Outlook where I keep all my contact-data of my customers. So, I want to select a customer from my Outlook contactlist when I am writing a new invoice in Excel. In Word, I have a macro which does this, but unfortunately the Application.GetAddress does not work in Excel. Can somebody help me ? "Henny Slokker" wrote: > Hello, > > I have an Excel sheet which I use as an invoicing-application. I would like > to retrieve address-data from Outlook where I...

ATLINE Function
Hello all I have received a spreadsheet with an ATLINE function, which Excel XP is just interpreting as a name, but does anyone know what this formula should be? Many thanks Richard From the net I get the following result which is nearly as confusing as your problem!! It appears to be a non XL comment. Cheers from North Yorkshire John AtCLine() Method AtCLine.doc Returns the line number of the first occurence of a string expression within another string expression without regard to case (upper or lower) http://portal.dfpug.de/dfpug/Kategorien/Sprachen/Visual%20FoxPro/ "richa...

IE8 uses 95+% of cpu after update to sp3
I just updated to XP sp3 and did updates after that and IE8 is running so slow. I loaded task manager to see what was happening and IE8 was using up to 99%. My system was a slow XP sp2 but did a disk clean and defrag and it was running a lot faster. Apps load and run faster after sp3 but not IE8. Thanks for any suggestions mx5 wrote: > I just updated to XP sp3 and did updates after that and IE8 is > running so slow. I loaded task manager to see what was happening > and IE8 was using up to 99%. > > My system was a slow XP sp2 but did a disk clean and defrag and...

Calculated fields in Pivot Tables
Is it possible to use an "if" formula when creating a calculated field? I have an existing field in my pivot table called commissions and I want to create a new field that will give me a 1 if for each row if commissions are over 4 and a 0 if they are under 4. Is this possible? What is the formula? It seems ok Try something like : =IF(Commissions>=4,1,0) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "lj" <lj@spu.edu> wrote in message news:1132008509.143327.194520@g47g2000cwa.googlegroups.com... > Is it possible to u...

VB: using a string to set a range object?
I'm a bit new to the excel "range" object type. I was suprised to see that while I can do: dim chunk as Range chunk = .Range(A5:B6) I apparently cannot do: dim chunk as Range dim stuff as string string = "A5:B6" chunk= .Range(string) How can I concatenate up a string describing a range, and then use it to define a range object's target cells? - Ross. Oops, I meant chunk = .Range("A5:B6") in the first example - I forgot the quotes. R. "RGK" <nothanks@nospam.go> wrote in message news:RqydnSWzbu_OEZbeRVn-2A@...

How to use CSplitterWnd for Dynamic Nested Splitter window?
Using VC++ (.NET) I am trying to implement a Dynamic Nested Splitter window using CSplitterWnd. I haven't found any documentation that explains how to get this to work properly and the only examples I have found also do not work properly. There are plenty of examples of Static Nested Splitter windows, but I need an example of a Dynamic one. The static splitter windows won't work for my requirements. Are there any examples or documentation that explains how to properly implement a Dynamic Nested Splitter Window using CSplitterWnd? Thanks, Dave Hi Dave, > Using VC++ (.NE...

Coding Convention for using Binary FlagWords?
Hi all --- I'm thinking of putting the .Tag property to use by using a binary ("bitwise") encoded flag word. Over time I've come across different possible uses for .Tag but have never standardized how I use it. Has someone developed a convention for "parsing" binary flag words? A simple IF works if I only need to test for one flag, but if I need to check for the presence of multiple bits the only construct that comes to mind is a series of IF statements .... something along the lines of: Enum FlagWord FlagA = 1 FlagB = 2 FlagC = 4 .......

Using Outlook client for CRM 3.0 in a remote office
I need to set up access to the corporate CRM from several remote offices. All of them have VPN connections (Windows or Checkpoint). What are the steps required to configure remote clients to be able to use CRM features in Outlook. Remote users can connect to CRM through Internet Expolorer. Thanks. Assuming they are connected, the isntallation over a VPN connection should not be any different than a typicaly installation. If the connection is slow though, the first synch & Go Offline process will be noticiably slower though. -- Matt Parks MVP - Microsoft CRM "mkatsev"...

pivot table subtotals
I'm stuck trying to develop subtotals in a pivot table. I hav successfully placed Sums of the individual elements, but I'd like "Subtotal" line for specific types of elements i.e.: 1/22 1/23 1/24 Group1 Apples Oranges Pears Group1 Sum Group2 Corn Carrots Beans Group2 Sum Grand Total All of thee above rows/lines calculate just fine except the GroupX Su lines? Does anyone have any suggestions on how I can get subtotals to wor correctly? Do -- Message posted from http://www.ExcelForum.com Double-click on the field button at the top of the Group column. Un...

using std::deque in multiple threads
I have a thread that listens on a message queue and populates a std::deque with events from that message queue, but only holds the latest 100. In my main thread, I want to populate a listbox with the information from that deque. So, it ends up being that one thread can add/remove items from the deque while another thread is trying to iterate through them. How can I make this thread-safe? Thanks, PaulH The code looks a bit like this: std::deque<MESSAGE_TYPE> m_dequeMessages; CMyClass::MessageThread() { //... while (ReadMsgQueue(hMsgQueue, &msg,...)) { m_dequeMessages.pu...

Use a wildcard within edit/replace
Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)),"",VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D:$E,2,FALSE)),"",VLOOKUP(TEXT($C8,"0000"),DataImport!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefo...

error message when trying to install WMP 10 or 11
My operating system is Windows XP Home Edition 2002. I'm trying to download either WMP 10 or 11 and I get a message saying it was not possible to complete setup, see webhelp for more assistance, however there is no additional information when I click Web help. the Error message is 8007F0DA. Can anyone help me? Thanks. On Tue, 30 Mar 2010 07:54:01 -0700, Nate <Nate@discussions.microsoft.com> wrote: > >My operating system is Windows XP Home Edition 2002. I'm trying to download >either WMP 10 or 11 and I get a message saying it was not possible to &g...

Wordwrapping a long sentence using F2, how to end the process?
Using Excel 2002...I pasted in a long sentence, and it goes off the right side of the screen. I know that I can make it word wrap by hitting F2. I've done that and it works nicely. But I was told that I could end this process by hitting Alt-Enter, and that doesn't work... all that happens after Alt-Enter is a blank new row opens up below the word-wrapped rows. If I hit Alt-Enter again, another blank row opens up below. How can I end this F2 word-wrap and go on with other business. John alt-enter is used to force a new line in a cell. If you want to wrap the text a...

excel 2000 message
excel 2000 message - 'cannot use object linking and embedding' Were they hit by the MSBlast worm? One poster (Lutz Meyer) guessed that this was the cause of his problems. I haven't seen any confirmation/denial, but you may want to read his post: http://groups.google.com/groups?threadm=3F3971AF.FA4490F5%40msn.com Post back with your results. I'm curious if that was the problem. (It's come up quite a few times since MSBlast hit.) bill bootle wrote: > > excel 2000 message - 'cannot use object linking and > embedding' -- Dave Peterson ec35720@msn.c...

Mobile using up CPU
Hi there Since we installed CRMMobile our (new) server has been SO SLOW! SqlServer is taking anywhere from 69 - 99% cpu time (mostly around 99) I have run sql profiler and discovered that many times a second the following transactions are running: exec sp_executesql N'SELECT recipientAddr,recipientSrvcPort,sourceAddr,sourceSrvcPort, MIN(arrivalDate) as minArrivalDate, count(*) as messageCount FROM vwInBoundQueue WHERE recipientAddr=@recipientAddr AND recipientSrvcPort=@recipientSrvcPort GROUP BY sourceAddr,sourceSrvcPort,recipientAddr,recipientSrvcPort ORDER BY minArrivalDat...

how can i start using excel for the first time?
i cant figure out how to get excel to work for me and im a first time user of it? i Cant get nothing to work on it? can anyone please help me here Paul Can you get Excel to start up? Can you get a blank workbook to open via File>New? For basics on Excel see.......... http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm Microsoft Training Courses. http://office.microsoft.com/en-us/training/CR061831141033.aspx Gord Dibben Excel MVP On Wed, 15 Dec 2004 15:39:02 -0800, "Paul Scheffer" <Paul Scheffer@discussions.microsoft.com> wrote: >i ...

Quartile Function
I would like to use a formula to return which quartile the given data array falls into- that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%, etc. The quartile function only returns the value of the various quartiles, while I need to know within which quartile the value falls. Any help is appreciated. On Tue, 15 Mar 2005 01:01:02 -0800, "tika528" <tika528@discussions.microsoft.com> wrote: >I would like to use a formula to return which quartile the given data array >falls into- that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%, >etc. &g...

File in use .... is locked for editing
Please help. In Microsoft Excel 2000, we seem to be getting the above error message frequently. No other users are in the file, and there aren't any temporary files relating to the file in question. Any ideas on what is causing this? We are running a mixture of Windows 2000 & XP machines on to a Small Business Server 2000. Hope to hear from someone soon Regards Simon I think you killed the usual answer of cleaning the temp folder. But have you rebooted? Maybe there's a hidden instance of excel running that "owns" the file. And if the file/workbook is on a net...

Counting Blank Cells in Pivot Table
How do I have a Pivot Table count the blank cells as well as the other cells? The Pivot table I created counts everything that had data (A, B, C, D, etc...) but does not count the blank cells. Thank you, Jack As answered in microsoft.public.excel.charting: To count the blanks you could use a space character, or the formula ="", in the source data, instead of leaving the cells blank. The pivot table will be able to count those. Jack wrote: > How do I have a Pivot Table count the blank cells as well > as the other cells? The Pivot table I created counts > everythi...

Specify data for use in FRx pivot tables
How do I specify what data fields appear in an Excel pivot table (or ..cub file) from FRx? I want to export the natural account number into the pivot table to sort on. TIA ...

using $ in a formula
when in a cell and I type: =$C4, what does that mean vs =C4? Also what does =$C$4 mean vs =C$4? what is the significance of the dollar sign? Thanks Hi Robin it changes the addressing from relative to absolute e.g. if i have in cell C1 the formula =A1*B1 and i fill it down to C2 the formula will change to =A2*B2 or if i fill it across to D1 the formula will change to =B1*C1 - this is relative addressing and is the default in excel however sometimes you want to "fix" the cell reference in a formula and this is where the $ come in =A1*$B$1 filled down from C1 to C2 will change ...

Code to make a Spinner button work with Filters on a Pivot Table
I create a lot of Pivot Tables with filter fields. Years ago I saw a demo on this tool and loved it. I got the code and my computer died before I was able to use it and my backup was a week old! Does anyone have this code? -- TheTraveler Hi Take a look on Debra Dalgleish's site http://www.contextures.com/PivotMultiPagesChangeAll.zip -- Regards Roger Govier TheTraveler wrote: > I create a lot of Pivot Tables with filter fields. Years ago I saw a demo on > this tool and loved it. I got the code and my computer died before I was > able to use it and m...

sumif using the now() function
I am trying to sum numbers using the NOW() function as my criteria, however no luck. sumif($D$1:$BC$1. ">=NOW()", D2:BC2) Any help would be appreciated. Thank you Try this: Assuming you try to sum D2 to BC2 on your example. =if($D$1:$BC$1>=Now(),sum(D2:BC2),"" -- renega ----------------------------------------------------------------------- renegan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1045 View this thread: http://www.excelforum.com/showthread.php?threadid=52871 renegan: Thanks for your response, however I got #VAL...

CString functions
I want to do the following to strings: 1) Check if first four characters are "DATA" 2) Get the middle 'word' from the following string "DATA 123 xyz" (the middle word is variable length) - extract the "123". 3) Get the last word/number from the following string "DATA SEND 1467436267" What functions should I use to achieve this? I'm new to C++ and finding string manipulation tricky. Gareth wrote: > I want to do the following to strings: > > 1) Check if first four characters are "DATA" > > 2) Get the middle &...

Using Option group to sort
I would like to use an Option group to sort records in a bound form, but am having difficulty getting it to work. I have used the function before without an issue, but this time I cannot get it to work as desired. Using Access 2003, I have a form bound to my Concern table. Within this form, I have a subform bound to my Countermeasure table. Each record in the Concern table is related to a record in the Countermeasure table. I want to be able to sort the records by their Status as it appears in the Countermeasure table - Specifically, Open, Closed or All records. When I used this before, t...