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

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

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