Count funtion in Pivot Table

[Guest]

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]

If it a number 1, not a text "1", you could use the Sum function in the
pivot table.

 

[Guest]

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 here:
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

 

[Roger Govier]

Hi

If your column does have text "1"'s rather than numeric 1's and you want
to leave this as your CF is based upon these text values, you could add
another column to your table and make it
=--(A1)
or whatever the column letter is for these values.
Copy down as required. Give the column header a name.
This will turn text values into Numeric's
Include this new column in your PT and Sum by this column