summary table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Monish - 20 Mar 2006 21:26 GMT

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 to this Message

Candyman - 20 Mar 2006 23:26 GMT

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.

> I have a table as follows:
>
[quoted text clipped - 18 lines]
>
> Thanks!!

Reply to this Message

Monish - 21 Mar 2006 06:30 GMT

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

> Have you tried a simple sumif function?
> For example your Week1/State1 cell would be something like:
[quoted text clipped - 29 lines]
> >
> > Thanks!!

Reply to this Message