I will need a formula showing counts, totals and sub-totals for data set from different sheet

Posted by Sapthagiri on Super User See other posts from Super User or by Sapthagiri
Published on 2012-12-18T14:19:25Z Indexed on 2012/12/19 11:05 UTC
Read the original article Hit count: 341

Filed under:
|
|

I am using MS2003 EXCEL. I have a cell in Sheet 1 with a color value and totals, with sub-totals. On sheet 2, I have a data set with 3 columns (colors, dress, type). On Sheet 1, I will need a tabulation showing Totals for Colors, with totals at sub-group of dress (shirt,pants) split by type totals (Full, Half, Tee)

Below table represents my Data set in Sheet 2

Colors  Make        Dress   Type
--------------------------------
Red     Arrow       shirt   full
Red     Levi        shirt   half
blue    Rugger      Pant    full
yellow  Wrangler    shirt   tee
yellow  Rugger      Pant    half
yellow  Arrow       shirt   tee
yellow  Wrangler    Pant    half
Green   Rugger      Pant    full
Red     Levi        shirt   tee
blue    Rugger      Pant    full
blue    Arrow       shirt   full
blue    Wrangler    Pant    half
Green   Levi        shirt   full

I will need a formula showing counts, totals and sub-totals on Sheet 1 for data set from Sheet 2.

Refer my table below which represent my expected data on Sheet 1,

    total   Shirt   Full    Half    Tees    Pants   Full    Shorts
Red     10     8      4      3        1        2      1        1
Blue                                
Green                               
Yellow                              

Please note I am not looking for a Pivot table solution.

© Super User or respective owner

Related posts about microsoft-excel

Related posts about excel-2003