Non-nested rows/columns in a pivot table?

[Guest]

Is it somehow possible to not have rows/columns nested?

Say, for example, that one field is 'colour' and another is 'year'. Is it
possible to have one column with 'colour' and another with 'year' rather than
a nested 'years per colour' or 'coulour per year' format?

I realize that it will give some problems with the sum rationale but it
would sometimes be useful nonetheless.

Thanks
Henrik

 

[Roger Govier]

Hi Henrik

Do you mean that you have both Colour and Year as row items?
If so, drag one of them to be a Column item instead, or drag one of them
to be a Page item.

 

[Guest]

Hi Roger

There would be other rows already, and you would have the intersections
displayed anyway, wouldn't you.

I'm looking for something like (not sure if the table will come out all
right):


Colour Year Total
Blue Red 2005 2006
Audi 50 25 60 15 75
BMW 20 25 15 30 45
Total 70 50 75 45 120

What I don't want is 'blue cars in 2005 / 2006', etc.

Henrik

 

[Roger Govier]

Hi Henrik

I don't think you can manage that with a PT.
You could drag one of the 3 fields to a page area, and therefore show
the other 2 as Row and Column values for that particular page selection,
or for All.

Assuming your data is set out in columns A as
Make Colour Number Year
If you then set out your table headings in G1:J1 as Red, Blue, 2005,
2006
and F2:F3 as Audi, BMW

then the following Sumproduct formulae
G2
=SUMPRODUCT(($B$2:$B$100=G$1)*($A$2:$A$100=$F2)*($C$2:$C$100))
copied to H2, and then G2:H2 copied down to G3

I2
=SUMPRODUCT(($D$2:$D$11=I$1)*($A$2:$A$11=$F2)*($C$2:$C$11))
copied in a similar manner
will produce the summary you want.

Change ranges to suit