MS Excel Pivots - Same Data used for Columns and Values 1

[WildbloodS]

Hi , this must be so straightforward but it's driving me nuts...
I need to evaluate Region, Area and Type in an excel 2010 pivot table.

Aim: I want to group data by Region, and Area in rows.
Then I want to be able to
1)

​

count occurrences of Type, while
2)

​

using Type as column Headings, and
3)

​

to also filter out Types I don't need to count

Should be simple ? Yet I only seem to be able to select the data source for one element.

I.E. Every time I try to set the Type as 'Column Label', it disappears from the other (e.g. 'Values'). Any pointers very gratefully received
Thanks all

WS

 

[dhookom]

Shouldn't you be able to "count" any column that has values? I expect you could add another column to the data if needed.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

Hi,

Drag Region & Area from the Choose fields area to the ROWS area

Drag the Type from the Choose fields area to the COLUMNS area

Drag the Type from the Choose fields area to the VALUES area and select COUNT of Type if not already selected

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[WildbloodS]

Thanks SkipVought, that's working fine now
WS

 

[dhookom]

WildbloodS,

It's appropriate when a member posts an answer to recognize the post and poster by clicking the "Great Post" link.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016