[SOLVED] Calculate percentage based on cells with conditional formatting

I have a column with conditional formatting (criteria 1 = green highlights /
criteria 2 = red highlights). I need to calculate percentages of the red
highlighted cells against the total # of cells (green and red). How do I
count only the red cells so that I can get the percentage?

use the same condition in the CF in the formula...

In article <BF24205E-0B05-40E2-93EB-5DBFD3A73B03@microsoft.com>,
"Cachod1" <Cachod1@discussions.microsoft.com> wrote:

> I have a column with conditional formatting (criteria 1 = green highlights /
> criteria 2 = red highlights). I need to calculate percentages of the red
> highlighted cells against the total # of cells (green and red). How do I
> count only the red cells so that I can get the percentage?

How do I write that formula? I tried =COUNTIF(N1:N382,"<today()") , but
it doesn't work.



"JE McGimpsey" wrote:

> use the same condition in the CF in the formula...
>
> In article <BF24205E-0B05-40E2-93EB-5DBFD3A73B03@microsoft.com>,
> "Cachod1" <Cachod1@discussions.microsoft.com> wrote:
>
> > I have a column with conditional formatting (criteria 1 = green highlights /
> > criteria 2 = red highlights). I need to calculate percentages of the red
> > highlighted cells against the total # of cells (green and red). How do I
> > count only the red cells so that I can get the percentage?
>

Try

=COUNTIF(N1:N382,"<" & TODAY())

In article <5C41617F-DED9-4DD5-91DB-E9F20F4205BC@microsoft.com>,
"Cachod1" <Cachod1@discussions.microsoft.com> wrote:

> How do I write that formula? I tried =COUNTIF(N1:N382,"<today()") , but
> it doesn't work.

Something still not right. Formula gives me a result of 186. I only have a
TOTAL of 183 rows and only 181 of them meet the criteria. Why am I getting
186?

Also, how can I include a second condition from another column into this
formula?
For example, I want to include in my final count all cells in column N that
meet both of the following conditions: from column N that are <today's date,
and from column O that are not equal to "x"

Or, how can I write the formula so that it only counts the cells from column
N that are both: <today's date AND that meets the conditional formatting
criteria I set up that highlights the cell if Column O is ="x"?



"JE McGimpsey" wrote:

> Try
>
> =COUNTIF(N1:N382,"<" & TODAY())
>
> In article <5C41617F-DED9-4DD5-91DB-E9F20F4205BC@microsoft.com>,
> "Cachod1" <Cachod1@discussions.microsoft.com> wrote:
>
> > How do I write that formula? I tried =COUNTIF(N1:N382,"<today()") , but
> > it doesn't work.
>

Hi,

Try this on a separate spreadsheet and apply the same principles to your
problem:

In column A, enter random numbers from A1 to A10 between 1 and 20.

In column B1, enter the formula '=IF(A1<10,"Red","Green")' and drag it down
to B10. This will display 'Red' if the number in column A is less than ten
and 'Green' if it is not.

In column C, enter randomly some 'x's. In cell D1, enter the formula
'=IF(AND(B1="Green",C1="x"),"BOTH","NOT BOTH"). If cell B1 contains 'Green'
and cell C1 contains an 'x', 'BOTH' will be displayed. This is the only
combination that will cause 'BOTH' to be displayed.

Hopefully you should be able to follow this example through and apply it to
your work.

Ewan.

"Cachod1" wrote:

> Something still not right. Formula gives me a result of 186. I only have a
> TOTAL of 183 rows and only 181 of them meet the criteria. Why am I getting
> 186?
>
> Also, how can I include a second condition from another column into this
> formula?
> For example, I want to include in my final count all cells in column N that
> meet both of the following conditions: from column N that are <today's date,
> and from column O that are not equal to "x"
>
> Or, how can I write the formula so that it only counts the cells from column
> N that are both: <today's date AND that meets the conditional formatting
> criteria I set up that highlights the cell if Column O is ="x"?
>
>
>
> "JE McGimpsey" wrote:
>
> > Try
> >
> > =COUNTIF(N1:N382,"<" & TODAY())
> >
> > In article <5C41617F-DED9-4DD5-91DB-E9F20F4205BC@microsoft.com>,
> > "Cachod1" <Cachod1@discussions.microsoft.com> wrote:
> >
> > > How do I write that formula? I tried =COUNTIF(N1:N382,"<today()") , but
> > > it doesn't work.
> >