[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
8
Medium Priority
?
250 Views
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.

Help!

B.
0
Comment
Question by:Bright01
  • 5
  • 2
8 Comments
 
LVL 23

Expert Comment

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

Author Comment

by:Bright01
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

by:NBVC
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))

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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bright01
ID: 38415222
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?
0
 

Author Comment

by:Bright01
ID: 38415268
I think a example would help..........
Sort-and-Add.xlsm
0
 

Author Comment

by:Bright01
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

by:
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

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

Appreciate the Teamwork.

B.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…

868 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