[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Calculation at the bottom of a Pivot Table

Posted on 2012-09-19
Medium Priority
250 Views
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.

Help!

B.
0
Question by:Bright01
• 5
• 2

LVL 23

Expert Comment

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

Author Comment

ID: 38414938
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
0

LVL 23

Assisted Solution

NBVC earned 1000 total points
ID: 38415159
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:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(\$A\$1:\$A\$100,ROW(\$A\$1:\$A\$100)-MIN(ROW(\$A\$1:\$A\$100)),,1)),--(\$B\$1:\$B\$100=1))

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

Author Comment

ID: 38415222

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?
0

Author Comment

ID: 38415268
I think a example would help..........
0

Author Comment

ID: 38415592
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?
0

LVL 34

Accepted Solution

Rob Henson earned 1000 total points
ID: 38417371
Have you looked at the SUBTOTAL function?

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

Thanks
Rob H
0

Author Closing Comment

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

Appreciate the Teamwork.

B.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
###### Suggested Courses
Course of the Month20 days, 10 hours left to enroll