Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting in Pivot Tables w/o "0"
I'm trying to add a formula in a pivot table, yet do not want to count "0".
Please help. bec |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting in Pivot Tables w/o "0"
What version of Excel are you using?
What is in the cells - text, numbers, dates? What is the formula you are using? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "bec" wrote: I'm trying to add a formula in a pivot table, yet do not want to count "0". Please help. bec |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting in Pivot Tables w/o "0"
Hi Bec,
Using the following data for a pivot table: Room Color Bathroom blue Playroom red Patio green Den 0 Kitchen yellow Drag the Room field into the Values area of the pivot table, then drag the Color field into the Row Labels area of the pivot table. You should end up with the following: Row Labels Count of Room 0 1 blue 1 green 1 red 1 yellow 1 Grand Total 5 Method 1 Select the cell with '0', then from the PivotTable Tools/Options/Tools click on Formulas, then select Calculated Item. Double click Color from the Fields list, then double click blue from the Items list - to create the following formula: = Color blue+ Color green+ Color red+ Color yellow include all fields to be counted (not able to exclude the one field that isn't counted) Result: Formula1 4 Method 2 Filter "0" out. Drag the field into the Report Filter area of the pivot table, then select multiple items, then deselect 0. This can be a problem is you are going to use this pivot table with new data - so check this filter. Method 3 Add a column to the source data and include the column in the pivot data source range, with the following formula: =if(cell=0,0,1) Sum this column in your pivot table to get a count. Hope this helps. Peggy "bec" wrote: I'm trying to add a formula in a pivot table, yet do not want to count "0". Please help. bec |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables show actual name rather then "Column Labels" | Excel Discussion (Misc queries) | |||
Pivot tables - how do I change default from "count of" to "sum of" | Excel Worksheet Functions | |||
Avoid "update Links" promt in e-mailed pivot tables? | Excel Discussion (Misc queries) | |||
Pivot Tables - How can I "reset" the selections in "Row Field"? | Excel Discussion (Misc queries) | |||
How do I disable "Get Pivot Data" when working from pivot tables? | Excel Worksheet Functions |