Count number of occurrences in excel pivot

[Akash Neel Hazarika]

Dear All,

I have an excel pivot report where data is in the following format..

A 23 12
B 34 3000
C 100
D 11

I just want the pivot to display the number of non blank cells with 1 and its summation below.

A 1 1
B 1 1
C 1
D 1

Total 3 3

May be , I am not able to describe my requirement in better way ,if you see the attachment , then only you will understand.


Regards

Akash

 

[xlstime]

Hey Akash,

Sorry but little confused, could you please share the sample output.

Dear All,

I have an excel pivot report where data is in the following format..

A 23 12
B 34 3000
C 100
D 11

I just want the pivot to display the number of non blank cells with 1 and its summation below.

A 1 1
B 1 1
C 1
D 1

Total 3 3

May be , I am not able to describe my requirement in better way ,if you see the attachment , then only you will understand.


Regards

Akash

 

[Akash Neel Hazarika]

Hi,

Pls find a sample output of a portion of the data. Only thing, i just want it to be done in the pivot itself.

Your help is very much appreciated.

Regards

Akash

 

[xlstime]

You can achieve this by using helper column in your raw data

paste below formula and drag till end and use this in your pivot table as sum.

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$G$2:G2,G2)=1,1,0)

Hi,

Pls find a sample output of a portion of the data. Only thing, i just want it to be done in the pivot itself.

Your help is very much appreciated.

Regards

Akash

 

[Akash Neel Hazarika]

Hi,

Can you pls revert me the same with the excel sheet i shared in my first post so that I can understand it easily. How to add helper column , i am bit confused.

A lot of Thanks in advance...

Regards

Akash

 

[Nebu]

Hi:

Is this what you are looking for?

Note:

I have not checked the results, please check and confirm.

Thanks

 

[Akash Neel Hazarika]

Hi,

The output is partially ok. 0 value is to be replaced with blank and total sum of values is not showing in the pivot table.

Pls help

 

[Nebu]

Hi:

I have taken care of 0's. For the subtotal part follow the below link to understand why it is giving 1's as subtotal.

http://www.contextures.com/excel-pivot-table-calculated-field.html

Thanks

 

[xlstime]

Enclosed, please check

Hi,

Can you pls revert me the same with the excel sheet i shared in my first post so that I can understand it easily. How to add helper column , i am bit confused.

A lot of Thanks in advance...

Regards

Akash

 

[Akash Neel Hazarika]

Awesome ! How did you do this can you pls guide a little.. and also pls tell me why have you saved the file with *.xlsb extension?

 

[xlstime]

Have used running count in your dump data to calculation number of occurrence then converted into 0-1 value and call in pivot table

for xlsb extn.

I have save in .xlsb to reduce file size ,you can save this with .xlsx also.

Awesome ! How did you do this can you pls guide a little.. and also pls tell me why have you saved the file with *.xlsb extension?