• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count number of occurrences in excel pivot

A

Akash Neel Hazarika

New Member
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
 

Attachments

  • C_Form.xlsx
    144.9 KB · Views: 7
Hey Akash,

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

Akash Neel Hazarika said:
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
Click to expand...
 
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
 

Attachments

  • sample_output.xlsx
    9.7 KB · Views: 7
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)


Akash Neel Hazarika said:
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
Click to expand...
 
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
 
Hi:

Is this what you are looking for?

Note:

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

Thanks
 

Attachments

  • C_Form.xlsx
    145.7 KB · Views: 4
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?
 
Last edited:
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.





Akash Neel Hazarika said:
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?
Click to expand...
 
You must log in or register to reply here.
Back
Top