Excel Magic Trick 1178

Conditional / AND Percentage: PivotTable Slicer, COUNTIFS or DCOUNTA?


From Survey Results, see examples of how to calculate the percentage of No responses given that the response was from a Full Time Faculty in the Business Division:
(00:58) Use PivotTable and Slicer
(02:25) In PivotTable use Show Values As “% of Column Total”
(03:11) Report Layout: Show in Tabular (To Show Field Names)
(03:22) Add Slicer to Filter PivotTable
(05:05) Use Two COUNTIFS functions. Including Copying and pasting COUNTIFS function a second time to get our percentage calculation.
(07:51) Percentage Number Formatting For Formula Results
(08:15) D Functions: DCOUNTA function
(10:37) Use Screen Tip Hot Link to open help on a particular function
(11:27) Why we have to use formula to enter Text Criteria for D Functions or Advanced Filter. Formula like: =”=No” for No Criteria. The Problem with just “No” is that it tells the D Function to do “Contains” or “Begins with” Criteria, which means it finds text that has “No”, so “No” and “Not Sure” are included in the count.
(13:05) Copy and Paste second DCOUNT to get percentage calculation.

Be the first to comment

Leave a Reply