Help Please with calculating large data in Pivot Table

Closed
r71lima
Posts
3
Registration date
Saturday August 3, 2013
Status
Member
Last seen
August 4, 2013
- Aug 4, 2013 at 01:01 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Aug 5, 2013 at 11:43 AM
Dear Sirs,

Can some one please help me with creating a better Pivot Table in Excel 2010. I work for an automotive company and so have to track all Models. I currently use a Pivot table to calculate the totals of each of the Modelsbut I need to break it down more than that. I also need to split within some of the models based on color, how many doors, Cylender size and whether it's 2wheel drive or 4.

I can't figure out how to get a Pivot table to go deeper in it's calculation with the criteria for changing all over the place like this. Can someone please help me come up with an idea as to what I can do? I would really appreaciate any help at all. A sample of my data is:

I need totals of models by Door/Cyl, 2wheel drive or 4 wheel drive, Hybrid or not. It's confusing to me if It's possible or not.

I need help please help with either a better Pivot Table, formulas or should I push it to Microsoft Access to Query it?? I have copied on of each of my line items. I need to have excel count how many of each of these I have plus all the duplicates in colors for each.

MODEL ID color Trim Trans Model Door Cyl

CT2B8DJNW RE/BK EX-LV6 6AT ACCORD 2 6
CR2E3DEW GY/BK LX 6MT ACCORD 4 4
CR2F3DEW BE/IV LX CVT ACCORD 4 4
CR3F8DJNW BK/BK EX-LV6 6AT ACCORD 4 6
YF3H2DEW SX/GR 2WD LX 5AT PILOT 5 6
YF4H4DEW SX/GR 4WD EX 5AT PILOT 5 6
RM3H3DEW BK/BK 2WD LX 5AT CR-V 5 4
RM4H5EJW BK/BK AWDEX 5AT CR-V 5 4
FB2F9DJNW WA/BK EX-L 5AT CIVIC 4 4
FB6E5DJVW WH/BK SI 6MT CIVIC 4 4
FG3B5DEW BX/BK LX 5AT CIVIC 2 4
TF1H5DKNW WA/BK 2EXLNV6 6AT CROSSTOUR 5 6
TF2H5DJNW BK/BK 4EXLV6 6AT CROSSTOUR 5 6
FB4F2DGW BK/GR HYBNAVI CVT CIVIC IMA 4 4

Thank you so much for reviewing for me,

R71lima

1 reply

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Aug 5, 2013 at 11:43 AM
Hi R71lima,

Let me know what you think of the following:
Creating named ranges:
With your example A3:A16 is named MODELID (same as the header)
B3:B16 is named color and so on.

Cell J3: Is dropdown list to choose header (Model, color, etc....)
Cell J4: You type in the specification (i.e. for color choose RE/BK or GY/BK or ....)
Cell J7: Contains formula which shows result.

So basically choose category, choose what you want to count within the category and see the result.

The above is applied in the following file:
http://www.ge.tt/7nspf2o/v/0?c

Best regards,
Trowa
0