Hello



I have a table with data as follows





A B

Animal Age

1 Cow 14

2 Sheep 23

3 Cow 87

4 Cow 123

5 Sheep 345

6 Sheep 22

7 Cow 12

8 Sheep 91



It is very simplified of course. I want to have a pivottable and in that
pivottable I want to see how many cows and how many sheeps there are within
a <90 and >90 age band. In other words I want to produce something like
this:



Animal Age < 90 Age >90

Cow 3 1

Sheep 2 2

Total 5 3



Is it possible to do that using calculated field (or other features of
pivottable). I wouldn't like to add any extra columns in the source table to
calculate the age band.

Regards



Igor

using function we can derive the results

try this

=COUNT(IF((A2:A10="cow")*(B2:B10<=90),)) ( use ctrl + shift + enter )

=COUNT(IF((A3:A10="cow")*(B3:B10>90),)) ( use ctrl + shift + enter )



On Nov 27, 12:07*pm, "IgorM" <ig...@live.com> wrote:
> Hello
>
> I have a table with data as follows
>
> * * * * * * A * * * * * * * * * * B
>
> * * * * * * Animal * * * * * *Age
>
> 1 * * * * *Cow * * * * * * * *14
>
> 2 * * * * *Sheep * * * * * * *23
>
> 3 * * * * *Cow * * * * * * * *87
>
> 4 * * * * *Cow * * * * * * * *123
>
> 5 * * * * *Sheep * * * * * * *345
>
> 6 * * * * *Sheep * * * * * * *22
>
> 7 * * * * *Cow * * * * * * * *12
>
> 8 * * * * *Sheep * * * * * * *91
>
> It is very simplified of course. I want to have a pivottable and in that
> pivottable I want to see how many cows and how many sheeps there are within
> a <90 and >90 age band. In other words I want to produce something like
> this:
>
> Animal * * * * * *Age < 90 * * * * * * * * * *Age >90
>
> Cow * * * * * * * *3 * * * * * * * * * * * * * * * * 1
>
> Sheep * * * * * * *2 * * * * * * * * * * * * * * * * 2
>
> Total * * * * * * * 5 * * * * * * * * * * * * * * * * 3
>
> Is it possible to do that using calculated field (or other features of
> pivottable). I wouldn't like to add any extra columns in the source tableto
> calculate the age band.
>
> Regards
>
> Igor

Hi,

First, what are you feeding the animals, I want some of it - sheep living to
345!

you don't need a calculated field

1. Remove the blank rows in your data
2. Select the data and choose Data, PivotTable and PivotChart Report, Next,
Next, Existing Worksheet, D1 (or any empty cell you choose), Finish.
3. Drag the animal field to the Row area
4. Drag the age field to the Column area
5. Drag the animal field to the Data area
6. Select the age titles 12 to 87 in the column area and choose PivotTable,
Group and Show Detail, Group
7. Type over the name Group1 with a title like <91 years
8. Select the age titles from 91 to 345 and repeat step 6. Name this >=91
Years.
9. Double-click the title <91 years (this will collapse it)
10. Double-click the title >=91 years (this will also collapse
11. Right click the grand total in the column area and choose Hide.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"IgorM" wrote:

> Hello
>
>
>
> I have a table with data as follows
>
>
>
>
>
> A B
>
> Animal Age
>
> 1 Cow 14
>
> 2 Sheep 23
>
> 3 Cow 87
>
> 4 Cow 123
>
> 5 Sheep 345
>
> 6 Sheep 22
>
> 7 Cow 12
>
> 8 Sheep 91
>
>
>
> It is very simplified of course. I want to have a pivottable and in that
> pivottable I want to see how many cows and how many sheeps there are within
> a <90 and >90 age band. In other words I want to produce something like
> this:
>
>
>
> Animal Age < 90 Age >90
>
> Cow 3 1
>
> Sheep 2 2
>
> Total 5 3
>
>
>
> Is it possible to do that using calculated field (or other features of
> pivottable). I wouldn't like to add any extra columns in the source table to
> calculate the age band.
>
> Regards
>
>
>
> Igor
>
>
>

It's a top secret mixture

I like your solution, but there is only one issue. What when new data is
entered and it's not grouped yet. The user (correct me if I'm wrong) has to
group it again. Is there a way to that in a way that will automatically
group them when the table is refreshed, so there is no need for user to play
with the pivottable structure?

Kind regards

Igor

U¿ytkownik "Shane Devenshire" <(E-Mail Removed)>
napisa³ w wiadomo¶ci
news:7369EAD2-EB8C-4F66-9650-(E-Mail Removed)...
> Hi,
>
> First, what are you feeding the animals, I want some of it - sheep living
> to
> 345!
>
> you don't need a calculated field
>
> 1. Remove the blank rows in your data
> 2. Select the data and choose Data, PivotTable and PivotChart Report,
> Next,
> Next, Existing Worksheet, D1 (or any empty cell you choose), Finish.
> 3. Drag the animal field to the Row area
> 4. Drag the age field to the Column area
> 5. Drag the animal field to the Data area
> 6. Select the age titles 12 to 87 in the column area and choose
> PivotTable,
> Group and Show Detail, Group
> 7. Type over the name Group1 with a title like <91 years
> 8. Select the age titles from 91 to 345 and repeat step 6. Name this >=91
> Years.
> 9. Double-click the title <91 years (this will collapse it)
> 10. Double-click the title >=91 years (this will also collapse
> 11. Right click the grand total in the column area and choose Hide.
>
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
> "IgorM" wrote:
>
>> Hello
>>
>>
>>
>> I have a table with data as follows
>>
>>
>>
>>
>>
>> A B
>>
>> Animal Age
>>
>> 1 Cow 14
>>
>> 2 Sheep 23
>>
>> 3 Cow 87
>>
>> 4 Cow 123
>>
>> 5 Sheep 345
>>
>> 6 Sheep 22
>>
>> 7 Cow 12
>>
>> 8 Sheep 91
>>
>>
>>
>> It is very simplified of course. I want to have a pivottable and in that
>> pivottable I want to see how many cows and how many sheeps there are
>> within
>> a <90 and >90 age band. In other words I want to produce something like
>> this:
>>
>>
>>
>> Animal Age < 90 Age >90
>>
>> Cow 3 1
>>
>> Sheep 2 2
>>
>> Total 5 3
>>
>>
>>
>> Is it possible to do that using calculated field (or other features of
>> pivottable). I wouldn't like to add any extra columns in the source table
>> to
>> calculate the age band.
>>
>> Regards
>>
>>
>>
>> Igor
>>
>>
>>