Pivot Tables - Combining multiple text columns in one pivot field

Hello everyone.

Ive recently started using pivot tables and learned a lot on my own but there's still one problem that I still can't solve.

I'm open to review the design of my workbook if need be, I just need external input because it seems like my brain can't figure this one.

I'll give a simple example to illustrate my problem:

Please Login or Register  to view this content.

My goal is to have a pivot table that will count the number of car models available for specific colors. Something like this:

Please Login or Register  to view this content.

How would you guys go about it? The solution is probably right in front of my eyes but for some reasons I can't figure it out.

All my effort were directed toward trying to combine the 3 columns into 1 field . A pivot field defined by a range if you will. I've tried adding Calculated fields/Items with no luck.

I'm using excel 2003

Looking forward to any suggestions or solutions.

Have a Great Day.

Phil

Thanks Donkey. I'll try both your solutions actually. I would like for the "Color" category to remain flexible and not have a fixed list. This way if new colors are entered, the pivot table would automatically count it.

I'll look into your multi consolidation solution and report back.