Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summary table
I have a table as follows:
Account State 1 State 2 State 3 Week ABC 12 13 141 1 XYZ 13 14 142 2 POP 155 444 1212 2 RSVP 15 28 144 1 XYZ 87 244 680 3 ABC 102 291 787 2 RSVP 117 339 895 2 RSVP 132 386 1003 3 and want to create a summary table as below: Week 1 Week 2 Week 3 State 1 State 3 State 1+2+3 I am sure this is fairly simpe and wold appreciate any suggestions. Thanks!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
summary table
Have you tried a simple sumif function?
For example your Week1/State1 cell would be something like: =SUMIF($E$2:$E9,1,$B$2:$B9) Week1/State3: =SUMIF($E$2:$E10,1,$D$2:$D10) Week1/State123 would be: =SUM(B15:B16)+SUMIF($E$2:$E11,1,$C$2:$C11) Change the center ",1," reference to ",2," or ",3," More complex would be to build an array and populate the array, trickier. "Monish" wrote: I have a table as follows: Account State 1 State 2 State 3 Week ABC 12 13 141 1 XYZ 13 14 142 2 POP 155 444 1212 2 RSVP 15 28 144 1 XYZ 87 244 680 3 ABC 102 291 787 2 RSVP 117 339 895 2 RSVP 132 386 1003 3 and want to create a summary table as below: Week 1 Week 2 Week 3 State 1 State 3 State 1+2+3 I am sure this is fairly simpe and wold appreciate any suggestions. Thanks!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
summary table
Thanks a lot...I knew it would be fairly simple. I am also having the first
table feed directly into Excel as a result of an Access query, so I will have the summary table automated and shold probably denote teh whole column (i.e. D:D) as I am not sure how long the query output will be each week. BTW, like your movies... "Candyman" wrote: Have you tried a simple sumif function? For example your Week1/State1 cell would be something like: =SUMIF($E$2:$E9,1,$B$2:$B9) Week1/State3: =SUMIF($E$2:$E10,1,$D$2:$D10) Week1/State123 would be: =SUM(B15:B16)+SUMIF($E$2:$E11,1,$C$2:$C11) Change the center ",1," reference to ",2," or ",3," More complex would be to build an array and populate the array, trickier. "Monish" wrote: I have a table as follows: Account State 1 State 2 State 3 Week ABC 12 13 141 1 XYZ 13 14 142 2 POP 155 444 1212 2 RSVP 15 28 144 1 XYZ 87 244 680 3 ABC 102 291 787 2 RSVP 117 339 895 2 RSVP 132 386 1003 3 and want to create a summary table as below: Week 1 Week 2 Week 3 State 1 State 3 State 1+2+3 I am sure this is fairly simpe and wold appreciate any suggestions. Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with worksheet table and summary | Excel Discussion (Misc queries) | |||
Pivot Table Summary | Excel Discussion (Misc queries) | |||
Creating summary table from detail table | Excel Worksheet Functions | |||
PIVOT TABLE - Summary Table into a Databasae Table. | Excel Worksheet Functions | |||
Pivot Table or Table Summary Help Please!! | Excel Programming |