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.
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
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
Asked:
3-
2
1 Solution
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
pivot_table_example.xlsx
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
=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
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.
Thanks again.
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 Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now