Excel Magic Trick 1178: Conditional / AND Percentage: PivotTable Slicer, COUNTIFS or DCOUNTA?

preview_player
Показать описание

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:
1. (00:58) Use PivotTable and Slicer
2. (02:25) In PivotTable use Show Values As “% of Column Total”
3. (03:11) Report Layout: Show in Tabular (To Show Field Names)
4. (03:22) Add Slicer to Filter PivotTable
5. (05:05)Use Two COUNTIFS functions. Including Copying and pasting COUNTIFS function a second time to get our percentage calculation.
6. (07:51) Percentage Number Formatting For Formula Results
7. (08:15) D Functions: DCOUNTA function
8. (10:37) Use Screen Tip Hot Link to open help on a particular function
9. (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.
10. (13:05) Copy and Paste second DCOUNT to get percentage calculation.
Рекомендации по теме
Комментарии
Автор

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:
1. (00:58) Use PivotTable and Slicer 
2. (02:25) In PivotTable use Show Values As “% of Column Total”
3. (03:11) Report Layout: Show in Tabular (To Show Field Names)
4. (03:22) Add Slicer to Filter PivotTable
5. (05:05)Use Two COUNTIFS functions. Including Copying and pasting COUNTIFS function a second time to get our percentage calculation.
6. (07:51) Percentage Number Formatting For Formula Results
7. (08:15) D Functions: DCOUNTA function
8. (10:37) Use Screen Tip Hot Link to open help on a particular function
9. (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 Criteria, which means it finds text that has “No”, so “No” and “Not Sure” are included in the count.
10. (13:05) Copy and Paste second DCOUNT to get percentage calculation.

excelisfun
Автор

It's really amazing. No one else could have explained this more precisely. I got a great Guru. You are simply awesome....

Pappuloveall
Автор

All the three methods are effective ! And It is always good to remove the dust from the DFunctions ! A good reminder for ppl that have not been exposed to them !

ExcelStrategy
Автор

So many ways to play with Excel :-)
Thanks !!!

BillSzysz
Автор

This is awesome.. great fan of your magic tricks

cagunjan
Автор

Same for me - after years of using it, I had discovered (some time ago) that the default advanced filter behaviour for text is "begins with..."
Thanks for pointing that out!

pmsocho
Автор

SO NICE VERY VERY THANKS, LOT OF THANKS

neccomputercenter
Автор

hehe Ctrl + backspace did not know about it :), sure will help

tomash
Автор

I have created similar pivot tables in which I've shown the results as a % of row total.  However, when I try to sort by percentage it doesn't work because the value isn't really the percentage, it's just "showing as percentage." Is there any way to "trick" excel into sorting by the percentage instead of the actual value in the cell?

gustavomeneses
Автор

How can I add my photo to my account, so that it appears on the top right corner in excel

bojmarlos
Автор

Hey! I was wondering if you could give me a code to lock and protect all worksheets, as well as allow the "Edit Objects" feature.  Thanks!

angjoi