How to count cells with conditional formatting

[Diana]

Hi!

I´ve used conditional formatting to color the cell from each column
that has de top value. I´m using Excel 2007 and I´ve created a rule
from the category Format only top or bottom ranked values. I´ve set it
to Top and instead of 10, I´ve put 1. This works fine. What I need to
do is to count by row how many cells are formatted with this rule. I
´ve tried to use count.if but I don´t know how to tell the function to
count only the cells that have conditional formatting because I can´t
use the same rule that I´ve used in conditional formatting.

Example

A B C

30 12 500
22 1 200
15 100 9

Top values are:

Column A: 30
Column B 100
Column C: 500

So if I count by row, I should get:

Row 1: 2
Row 2: 0
Row 3: 1

Thanks for your help!!

 

[Bernard Liengme]

This is my worksheet
30 12 500 row 1 2
22 1 200 row 2 0
15 100 9 row 3 1
The formula in F1 (to the right of "row 1") is
=COUNTIF(A1:C1,MAX(A:A))+COUNTIF(A1:C1,MAX(B:B))+COUNTIF(A1:C1,MAX(C:C))
This is copied down to F3

best wishes

 

[helene and gabor]

Hello Diana,

Copy formula say F1 to F8.

=--(MAX($A$1:$A$8)=A1)+ --(MAX($B$1:$B$8)=B1)+--(MAX($C$1:$C$8)=C1)


HTH

Gabor Sebo
Hi!

I´ve used conditional formatting to color the cell from each column
that has de top value. I´m using Excel 2007 and I´ve created a rule
from the category Format only top or bottom ranked values. I´ve set it
to Top and instead of 10, I´ve put 1. This works fine. What I need to
do is to count by row how many cells are formatted with this rule. I
´ve tried to use count.if but I don´t know how to tell the function to
count only the cells that have conditional formatting because I can´t
use the same rule that I´ve used in conditional formatting.

Example

A B C

30 12 500
22 1 200
15 100 9

Top values are:

Column A: 30
Column B 100
Column C: 500

So if I count by row, I should get:

Row 1: 2
Row 2: 0
Row 3: 1

Thanks for your help!!

 

[Jayanta Boral]

Dear Diana,

You can use following formula in next 3 columns , say D,E and F. Sumtotal of
D,E,F at column G will give the answer.

supposing data has been put in row 2 onwards, and row 1 is the header - For
Column D, use the following formula :

IF(A2=MAX(A$2:A$4),1,0)

Copy the cell and paste the formula in D and E column.

In column G simply sum columns D,E and F.

Regards,

Hi!

I´ve used conditional formatting to color the cell from each column
that has de top value. I´m using Excel 2007 and I´ve created a rule
from the category Format only top or bottom ranked values. I´ve set it
to Top and instead of 10, I´ve put 1. This works fine. What I need to
do is to count by row how many cells are formatted with this rule. I
´ve tried to use count.if but I don´t know how to tell the function to
count only the cells that have conditional formatting because I can´t
use the same rule that I´ve used in conditional formatting.

Example

A B C

30 12 500
22 1 200
15 100 9

Top values are:

Column A: 30
Column B 100
Column C: 500

So if I count by row, I should get:

Row 1: 2
Row 2: 0
Row 3: 1

Thanks for your help!!