Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count funtion in Pivot Table
I'm trying to use a pivot table to count the results of a IF statement which
has conditional formatting attached. The if statement returns a "1" results if the column date falls within a specified date range. The conditional formatting then assigns this cell a certain colour. I would like to input this data into the Pivot Table along with other row data in order to report on it. Currently when I do this Excel takes every cell as having a value due to the IF statement / conditional formatting. I have been able to calculate the total number of cells in the column which contain a "1" value by doing a SUMIF with a VALUE field for the "1" but I don't know if I can do this with the Pivot Table. Hope that all made some sense. Any ideas on how to include this data in a pivot table? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count funtion in Pivot Table
If it a number 1, not a text "1", you could use the Sum function in the
pivot table. Aussie Mick F wrote: I'm trying to use a pivot table to count the results of a IF statement which has conditional formatting attached. The if statement returns a "1" results if the column date falls within a specified date range. The conditional formatting then assigns this cell a certain colour. I would like to input this data into the Pivot Table along with other row data in order to report on it. Currently when I do this Excel takes every cell as having a value due to the IF statement / conditional formatting. I have been able to calculate the total number of cells in the column which contain a "1" value by doing a SUMIF with a VALUE field for the "1" but I don't know if I can do this with the Pivot Table. Hope that all made some sense. Any ideas on how to include this data in a pivot table? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count funtion in Pivot Table
I don't believe that you can use pivot tables to take into account the number
of cells to which conditional formatting is applied, at least without writing a macro. You can, however, use the COUNTIF function to count the occurrences of a certain date. Assume the condition you're interested in counting is dates that show 10/1/2006 (using the American mm/dd/yyyy format), and the dates in question are in column A. =COUNTIF(A1,A100,"10/1/2006") will count all the records for which the corresponding date is 10/1/2006. Does that do what you're looking to accomplish? There is, however, a way to count colored cells with macros. See he http://www.cpearson.com/excel/colors.htm Personally, I think this is much more complicated than the method I give above. Post back if you have questions. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Aussie Mick F" wrote: I'm trying to use a pivot table to count the results of a IF statement which has conditional formatting attached. The if statement returns a "1" results if the column date falls within a specified date range. The conditional formatting then assigns this cell a certain colour. I would like to input this data into the Pivot Table along with other row data in order to report on it. Currently when I do this Excel takes every cell as having a value due to the IF statement / conditional formatting. I have been able to calculate the total number of cells in the column which contain a "1" value by doing a SUMIF with a VALUE field for the "1" but I don't know if I can do this with the Pivot Table. Hope that all made some sense. Any ideas on how to include this data in a pivot table? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table: how to count unique? need help! | Excel Discussion (Misc queries) | |||
Derived Columns in Pivot Table | Excel Discussion (Misc queries) | |||
Count unique values - Pivot Table | Charts and Charting in Excel | |||
pivot table created from another pivot table | Excel Worksheet Functions | |||
Pivot table Data always showing up as "Count" | Excel Discussion (Misc queries) |