Calculation at the bottom of a Pivot Table

Posted on 2012-09-19
Last Modified: 2012-09-20
EE Pros.

I have a Pivot Table and it, as you can imagine, changes often.

I want to count the records that are displayed or have a non-blank field at the bottom of the Pivot Table so that when the Table changes, it recalculates the sum.


Question by:Bright01
    LVL 23

    Expert Comment

    How is that different than the Grand Total that the Pivot Table displays by default when you create it?

    Author Comment

    My mistake!!!  Sorry.  It's not a pivot table.  I have a table with the sort function.  What I'm trying to do is at the bottom of the sort function, create a function that will add all the records that are displayed due to the sort.  

    So for instance, I have a list of 100 items where 45 of the items have a cell next to them that has a 1 and 55 that are blank.  When I sort on "1", I want 45 as the sum at the bottom; when I select all, I want 100.  Right now I'm using a Countif function but that counts them all even when sorted.

    Sorry for the confusion.

    This time, Not so Bright
    LVL 23

    Assisted Solution

    If you only have 1's and blanks, and you want to sort on 1, then COUNTIF(A1:A100,1) will give you the same result of 45 whether filtered or not.....

    If you are filtering on another column, say A1:A100 and want to count the number of 1's in column B after the filter, then:


    adjust ranges to suit.

    if the sheet is constantly growing, then you should put this formula above the table or in top row in a new column.

    Author Comment

    Very helpful!  

    If I want the total changes, as you "filter", in say Columns A1:C100 (3 columns) how would the formula look?

    Is the formula you provided flexible enough that I put it at the top/bottom of the sheet and then regardless of the filter I select, it will act on the column below?

    Example:  The data for the Table is A1:F100.  I'm going to filter on column A and want to display the number of instances from Columns A, B, and C.  Then I'm going to filter on column B and it will become a subset.  Will it calculate the new totals?

    Author Comment

    I think a example would help..........

    Author Comment

    Just to add a little more clarity:  When I filter a particular column, I want the numbers at the bottom (In col. A, B and C) to reflect the new totals.  If I filter two columns, again, new totals......does that make sense?
    LVL 31

    Accepted Solution

    Have you looked at the SUBTOTAL function?

    The results of subtotal change dependent on visibility of rows, only visible rows will be included.

    Rob H

    Author Closing Comment

    Thanks guys!  Subtotal function did the trick.  Simple and clear.

    Appreciate the Teamwork.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now