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
639 Views

Similar Articles

[PageSpeed] 37

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:

Find cell value within table
I would like to find if the cell value of A1 exists in table A2:AA450 I have tried with ISNA and Match but Match does not seem to work as I have more than one column and row: Match(A1,A2:AA450,0) returns #N/A Any idea? thanks -- caroline Maybe =countif() would work: =if(countif(a2:aa450,a1)>0,"it's there","nope") caroline wrote: > > I would like to find if the cell value of A1 exists in table A2:AA450 > I have tried with ISNA and Match but Match does not seem to work as I have > more than one column and row: > Match(A1,A2:AA45...

Using this News Group
I would appreciate it if someone could tell me if I have a setup problem on my computer or there is sometimes a problem with this news group when I access it IE7. After I sign in and see an item that I want to reply to, I press on reply. After I clicked on the reply sometimes the window pos up and I am all set to reply the way I expect other times I get no response or I just get the same message that I was looking at. Anyone else experiencing this issue? "neil154" <neil154@discussions.microsoft.com> wrote in message news:A345E68D-C3B9-4FD8-8211-CACE1374C389@microso...

Subreports containing Pivot tables and charts
Hello, I have a main report that contains subreports. One of them has a pivot table and another has a pivot chart. When I try to export them to MS Word, the pivot table and chart don't show in the word document.... Any pointers to what could be the problem is very much appreciated... Thank you very much, Nora ...

trying this out
This is a test posting. On Tue, 13 Nov 2007 16:06:51 +0800, "hmmmm" <bobo123@dotdot.dyndns.org> wrote: >This is a test posting. > Please use microsoft.public.test or microsoft.public.test.here for your testing. This is a working newsgroup. If you have questions about an Access database, please post them. John W. Vinson [MVP] ...

Recorded macros not working using the shortcut keys
Hello, I have recorded two macros that work fine except they do not run using the shortcut keys I defined. I've stepped through the macros and the shortcut keys are in the scripts, but I can only run them by pulling up the list of macros and selecting the macro and clicking run. I'm using Office 2007 in XP. TIA, Robert When you pull up the list of Macros and select your macro, go to the options box and create a shortcut key. On Fri, 13 Feb 2009 06:11:40 -0800 (PST), CurlyDave <davesexcel@gmail.com> wrote: >When you pull up the list of Macros and select your macro, go to...

Need to compare 2 tables and only pull certain records from one ta
I have a monthly sales file that contains several invoices that allows duplicate records of parts. My second table is a different sales file that also contains several invoices and allows duplicate records. I want to match from the second table as a basis. How do I pull matching invoices from the two tables without pulling all the data from the first monthly sales file? UNTESTED UNTESTED SELECT SecondTable.* FROM SecondTable INNER JOIN FirstTable ON SecondTable[Invoices] = (SELECT FirstTable.[Invoices] FROM FirstTable GROUP BY FirstTable.[Invoices]) ORDER BY SecondT...

I am trying to round up to the nearest 25 cents
I am trying to round a money value to the nearsest 25 cents and it keeps rounding to the nearest dollar HELP Try =CEILING(A1, 0.25) In article <2DB8F007-1E27-4A48-923B-C7D4D0238B5E@microsoft.com>, sypher <sypher@discussions.microsoft.com> wrote: > I am trying to round a money value to the nearsest 25 cents and it keeps > rounding to the nearest dollar HELP =ROUND(A1*4,0)/4 -- HTH Bob Phillips "sypher" <sypher@discussions.microsoft.com> wrote in message news:2DB8F007-1E27-4A48-923B-C7D4D0238B5E@microsoft.com... > I am trying to round a mone...

Trying to clear up Received but not invoiced report
I am trying to clean up the received not invoiced items. The problem began with entering a purchase order and then not matching the invoice to the items received agaisnt the purchase order. SO we thought that doing a returned transaction entery would solve the problem. The only thing is now the return shows up on our Received/ Not invoiced report. And the report is becoming a huge mess and difficult to use. What is the best way to clear up items that have been received but were not matched and already paid through the invoice entery? There is a knowledgebase article on this very thing av...

C++ Version 6 app using c++ version 8 dll
Hello all, My company is in the process of migrating to Visual Studio 2005. For now only new development is happening in 2005, leaving existing programs in version 6. I have developed a MFC version 8 dll. A version 6 application needs to link to this new dll. The problem I am having is related to STRINGS. The dll exports a few functions that have strings as parameters. These function generated linker errors. TestDlg.obj : error LNK2001: unresolved external symbol "int __cdecl Test(char const *,char const *)" (?Test @@YAHPBD0@Z) Test.obj : error LNK2001: unresolved external ...

Do i have to use an HOTMAIL account for Windows Live Mail?
I'm so frustrated! I just got a new website for my business and I am trying to set up my e-mails through windows live but it keeps sending me errors. I am inputting all my information as the guy told me to but it still isnt working. Apparently my e-mail will work in OUTLOOK though. Can anyone tell me if i have to use an hotmail account or if i can use my own such as nicole@gmpearson.ca Please post the error message in its entirety. You can left click on it to highlight and then right click to copy and then paste it into this thread. -- Bruce Hagen MS-M...

send/receive (2nd try)
New computer. Microsoft XP Home. Microsoft Office XP Small Business 2002. Earthlink dial-up account. Two e- mail accounts. After opening Outlook, 1st send/receive (option 3, all accounts) usually prompts for both passwords, downloads messages, all good. Next time, though, no prompt for account 2, error cannot connect to server, account 1 usually gets messages. Every five minute send/receive option eventually degenerates to cannot connect to server errors for both accounts. Meanwhile internet browsing slows to a crawl. Any ideas? ...

How do I cancel a msg Outlook keeps trying to send unsuccessfully?
How do I cancel an "in process" email Outlook keeps trying to send unsuccessfully due to a huge email message I tried to send with way too many attachments? It's stuck in "send" mode, and as a result, I can't send any emails because Outlook cannot get past trying to send this message. How do I cancel a pending outbox transmission while it's trying to send? I've tried everything I can think of to no avail. Please help! Angelczech wrote on Fri, 12 March 2010 07:32 > How do I cancel an "in process" email Outlook keeps trying to send ...

Join same field on two tables
Forgive if this is really stupid. This is what I am dealing with in terms as simple as I can come up with: I have one table with multiple records (country names) and multiple fields (over 20). I have a second table with that contains only a limited number of records (all of which are also represented in the other table) with only two fields (also represented in the other table). I need to know if it is possible to make the information from the second table "join" with the first table and display the data from both tables in one column. Does that make any sense? You can combine da...

Count of unique values in Pivot Table
Hello, I have exported values from a defect tracking system into excel Each defect is categorized in one of a couple of hundred different categories. I want to somehow display the count of the unique categories (and not the number of defects asssociated with each category). Is there a way to do this using Pivot table functions -- as opposed to exporting the list, using advanced filters, etc. Thanks in advance BAM Check Debra Dalgleish's site: http://contextures.com/xlPivot07.html#Unique bam wrote: > > Hello, > I have exported values from a defect tracking system into ...

VLOOKUP use column name not number
Is there a way to use VLOOKUP to search for a column name and return the value in that column, rather search for a column number. -- Thank you, Kathy kathy wrote: > Is there a way to use VLOOKUP to search for a column name and return the > value in that column, rather search for a column number. > Use INDEX/MATCH. Look here: http://www.contextures.com/xlFunctions03.html#IndexMatch2 Something like this... =VLOOKUP("this",A:J,MATCH("column_name",A1:J1,0),0) -- Biff Microsoft Excel MVP "kathy" <kathy@discussions.micros...

trying to insert a 0 in front of zipcode
Hello, I do not use excel at all so i have no clue! I have a list of daycares that i want to create mailing labels with...about 700 of them but the problem is my zipcode column there needs to be a zero in front of all 700 zipcodes how to i insert a zero in front of them without having to type it into each box? the other question is can i print labels directly from excel or do i have to go thru ms word? thanks Greg One of these 1) format the cells with custom format 00000 2) format the cells with Special / Zip Codes 3) format the cells as Text 4) begin all zip codes with an sin...

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

Try or TRY
Hello, Will there be any opitmization or ... when we use INT or TRY macros instead of int or try ? Which one is better to be used? Thanks, JSmith "JSmith" <jsmithmitra@yahoo.com> wrote in message news:%23G1sOVdoEHA.692@TK2MSFTNGP12.phx.gbl... > > Hello, > > Will there be any opitmization or ... when we use INT or TRY macros instead > of int or try ? Which one is better to be used? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore/html/_core_exceptions.3a_.converting_from_mfc_exception_macros.asp -- Jeff Partch [VC++ MVP] FYI, TRY ...

< and > not showing in pivot table
I have created a pivot table placing a date field in the column box. have given a date range to display (5/1/2004-12/31/2005). All of th months within this range are displaying fine but I also need a colum for the <5/1/2004 and >12/31/2005. These show up under th PivotTableField popup in the Hide Items box but even though they ar not highlighted they don't show up on the table. I am running Offic 97. Any help would be appreciate -- Message posted from http://www.ExcelForum.com Is there data for those date ranges for the selected row fields? Reds < wrote: > I have created a...

Money 2004: Got an error attempting to use ePay
I recently purchased MS Money 2004 Deluxe. I decided to use the MSN Bill Pay (I have never used it before). Tonight I sat down to pay a couple of utility bills. When I did each generated the following error message: "Your payment was not processed for the following reason: An Internet communication error occurred. Please try your call again later. (OFXIE12029)" What does this mean? And what about the bills I attempted to pay? Are they paid, or not? And if not, then how do I get them resubmitted to be paid? Rod ...

Help with Function: Between Time
Hi, Why this function is not working? If a time follows between 12AM and 3PM it should show Day shift, anything else Night Shift If Time > #12:01:00 AM# < #3:00:00 PM# Then MsgBox "DAY SHIFT" Else MsgBox "NIGTH SHIFT" End If If Time > #12:01:00 AM# AND Time < #3:00:00 PM# Then MsgBox "DAY SHIFT" Else MsgBox "NIGTH SHIFT" End If Note that you need to repeat the entire comparison. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Bre-...

error message when trying to delete messages in inbox
I cannot delete messages in my email inbox. When attempting to delete messages, Outlook says "The messaging interface has returned an unknown error. If the problem persists, restart Outlook. I continue to get the message even though I restarted Outlook. My mail works fine, but I just cannot delete anything. Does someone know a solution? Can you delete them if you hold shift as you delete? How many items are in the deleted items folder? Are you using any type of AV scanner on the message store? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coa...

Spelling in new Security Tables in GP v10
There are a significant number of spelling mistakes in the Dynamics SY09000 and SY09100 tables in GP 10.0. These are the new Security Role and Task Master tables. Please correct these for future releases. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www...

Help in Creating Relationships in a table
Hi This is a follow to a previous question concerning relationships in a table. These are my tables I have in my database. Students StudentsID LastName FirstName HomeroomTeacher (ohter info relating to the student) Cases CaseNo_ID <PK> StudentID <FK> Categories CategoryID <PK> Category (e.g. School, Community, Individual, Peers, Family) Issues IssuesID <PK> Issues (text, e.g. lack of role models) CategoryID <FK> StudentIssues CaseNoID <link to Cases Table> IssuesID <Link to issues Table> ...

Date format wrong when using shortcut key
I use Word 2007. I set the default date format by clicking Date & Time in the Text section under the Insert tab on the ribbon. I choose the format I want and click Default. I make sure the “Update automatically” box is not checked. Then I click OK. The date is inserted in the format I chose and it does not automatically update. Everything works fine up to this point. My problem comes when I then try to insert the date using the keyboard shortcut Alt+Shift+D. According to several Word 2007 discussion groups, when Alt+Shift+D is used the date should be inserted with the default...