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

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.

-

Without a pivot table, =COUNTIFS would seem the next best bet, but since you do not mention which version of Excel, using =COUNTIF:

Add a helper column in Sheet 2 that concatenates Color/Dress/Type. For example, with labels in Row1 and Color in ColumnA, Dress in ColumnB and Type in ColumnC =A2&B2&C2 say in ColumnD, copied down as necessary, say to Row21.

Assuming Sheet 1 has column labels and columns are in order as indicated, without gaps, starting at ColumnA:

In B2 =C2+G2
In C2 =SUM(D2:F2)
In D2 =COUNTIF('Sheet 2'!\$D\$2:\$D\$21,\$A2&\$C\$1&D\$1)
In E2 =COUNTIF('Sheet 2'!\$D\$2:\$D\$21,\$A2&\$C\$1&E\$1)
In F2 =COUNTIF('Sheet 2'!\$D\$2:\$D\$21,\$A2&\$C\$1&F\$1)
In G2 =H2+I2
In H2 =COUNTIF('Sheet 2'!\$D\$2:\$D\$21,\$A2&G\$1&H\$1)
In I2 =COUNTIF('Sheet 2'!\$D\$2:\$D\$21,\$A2&G\$1&I\$1)

And copy down B2:I2 as far as required.

-
Thanks a lot for your reply, though your solution helps me to an extent, I would need one where I would not required to concatenate the column values. Is that possible? SUMIF would help me solve this? Also to answer your query on my version of EXCEL, I am using a 2003 MS EXCEL. – Sapthagiri Dec 19 '12 at 9:16
Excel 2003 does not have a COUNTIFS function (SUMPRODUCT might work instead). – pnuts Dec 19 '12 at 10:52

Hurrah!! I could finally resolve it using SUMIF formulae, it serves my purpose of showing total and sub-total counts. I could achieve it by creating multiple levels of IF loops and SUMming them up.

Find my solution as stated below,

Total count by Colors

=IF(N29="","-",SUM(IF(Sheet2!A\$2:A\$50=Sheet1!A2,1,0)))

Total count by Dress

=SUM(IF(Sheet2!A\$2:A\$50=Sheet1!A2,IF(Sheet2!C\$2:C\$50="Shirt",1,0),0))

Total count by Type

=SUM(IF(Sheet2!A\$2:A\$50=Sheet1!A2,IF(Sheet2!C\$2:C\$50="Shirt", IF(Sheet2!\$D\$2:\$D\$50="Full",1,0),0),0))

-