• 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.

Formula for pivot table

Y

ydmani

New Member
Greetings,

I am not awesome in excel and now have stepped forward to become one :)

I have created a database table (Master), formatted it as a table and a pivot table based on that table.

My problem no.

1) I am not able to SUM the values of a column in a pivot table according to their record. Like my pivot table is fetching 2 records for VARUN with two individual figures. I wanted to SUM those 2 numerical figures into one. (please see the attached file for better understanding of my problem)

2. Please help me out in removing those unwanted and irritating "(blank)" from the pivot table.
 

Attachments

  • Sample excel file snapshot.jpg
    Sample excel file snapshot.jpg
    406.1 KB · Views: 35
1) You should be able to right click on one of Row label and collapse to summarize. In this case Company Booking Rate.

2) Go to filter button below Block name and filter out (blank). If any remains, replace (blank) with single space.
 
Those values won't sum because you don't have the same values in 'Booking Date', 'Booking Rate', 'Company Booking Rate', and 'Company Cost' for the two items. So Excel treats them as two different items. You either need to make the data in those columns exactly the same, or remove those specific columns from the PivotTable, or group the items together using the Group function (see Google for more on that)
 
Chihiro said:
1) You should be able to right click on one of Row label and collapse to summarize. In this case Company Booking Rate.

2) Go to filter button below Block name and filter out (blank). If any remains, replace (blank) with single space.
Click to expand...

Thanx Chihiro, blanks are gone but the other problem remains same. However, I manage to resolve the issue with some other method (although I don't like that).
 
jeffreyweir said:
Those values won't sum because you don't have the same values in 'Booking Date', 'Booking Rate', 'Company Booking Rate', and 'Company Cost' for the two items. So Excel treats them as two different items. You either need to make the data in those columns exactly the same, or remove those specific columns from the PivotTable, or group the items together using the Group function (see Google for more on that)
Click to expand...

Thanx Jeffrey, things are little complicated, but I manage to resolve the issue with some other method.
 
You must log in or register to reply here.
Back
Top