• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 39
  • Last Modified:

Pivot Table Summary Help

Using DCount on a pivot table.  

I have a pivot table that is giving me just what I want to know, but now I need to make a small summary of how many rows I have and then how may rows to I have when the value in a column is over 1.  I have a filter on the pivot so it is a dynamic range that I am working with ,  The summary section I have is seperate from the pivot table.  

Attached is a screen shot of the what I am looking for.  The rows with the check mark I have I am looking for the other two in the summary Number of Physicians 24 and Number of Physicians with MSO cases over 48 hours which is 17.  I did name my pivot table MSO_CASES.

I am using Excel 2007

Thanks,
Rodger
pivot_table_example.JPG
0
Rodger
Asked:
Rodger
  • 3
  • 2
1 Solution
 
Rob HensonFinance AnalystCommented:
Can you upload that sample as an Excel file rather than screenshot?
0
 
RodgerSystems AnalystAuthor Commented:
Here is a sample of the pivot table I wam working with.  I had to remove a lot of the data as it is PHI so if I missed something I am sorry.  It should be OK.  The dummy column is used when I need to do a calculation in pivot table I just did not include it in the example, but I hvae another field that calculate the percentage for each physician of how many cases were over 48 hours.
pivot_table_example.xlsx
0
 
Rob HensonFinance AnalystCommented:
For count of physicians is it not just a count of column A??
   =COUNTA(A:A)-3

COUNTA counts cells that are not empty so will count the headers; -3 is to allow for them.

In the same vain, the count of physicians with cases greater than 48 hours will be a COUNTIF of column C
   =COUNTIF(C:C,">0")-1
The COUNTIF will count the total row so needs reducing by 1.

Percentage of physicians with cases greater than 48 hours will then be:
   =H7/H6
0
 
RodgerSystems AnalystAuthor Commented:
WOW it was that dimple.  I was looking at doing some advanced filtering.  Trying to figure out how I could use the pivot table functions.  Glad to see it was something simple.

Thanks again.
0
 
Rob HensonFinance AnalystCommented:
I did wonder if there was a more complicated way but sometimes simpler the better.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now