excel count cell with color conditional formatting

[Marco]

Can i count number (sum) of cells in a column colored with conditional
formatting ?

thx

the formula is =ISNUMBER(MATCH(D3;$AL3:$AR3;0))
column D3400
color red

 

[Bob Phillips]

It' difficult.

See http://www.xldynamic.com/source/xld.CFConditions.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Can you not just use the same logic that colours it within a formula?

=SUMPRODUCT(--ISNUMBER(MATCH(D3400;$AL$3:$AR$3;0)))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Marco]

Can you not just use the same logic that colours it within a formula?

=SUMPRODUCT(--ISNUMBER(MATCH(D3400;$AL$3:$AR$3;0)))

Thanks for the answer, I try to explain in English.
Nobody of the suggested methods seems to work in my case, if I have
understood.
The method of Bob Phillips why i use the list separator semicolon ( in
the formulas, instead of comma (,) , therefore it gives back # value! in
result cell.
Your formula why my comparison is made only for row, and i comparison every
single cell with a range (AL:AR) of the same fixed row .
=ISNUMBER(MATCH(D2;$AL2:$AR2;0)) =ISNUMBER(MATCH(E2;$AL2:$AR2;0))
=ISNUMBER(MATCH(F2;$AL2:$AR2;0))
=ISNUMBER(MATCH(D3;$AL3:$AR3;0))
=ISNUMBER(MATCH(E3;$AL3:$AR3;0))=ISNUMBER(MATCH(F3;$AL3:$AR3;0))
.....

I don't know if exist an other solution without conditional formatting for
this problem: knowing the number of cells in a column, that cell is equal to
range in the row.
The my real rows are thousand approximately every 2-4 months.
For now i have characterized 3 solutions:
1) to count them by hand .
2) change the settings of the computer and try to use Bob Phillips formulas.
3) to wait for until 2006 office 12 , sort and filter by color ( count color
will be better), fantastic for a profane like me.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

Best regards

 

[Ken Wright]

For now i have characterized 3 solutions:

1) to count them by hand .

Would never consider that an option. Lots of ways to do what you want with
helper columns etc. Nice and neat to do it with a single formula if possible
though.

2) change the settings of the computer and try to use Bob Phillips
formulas.

You could try this and then just change the settings back, and I think it
would all adjust accordingly - not sure though.

3) to wait for until 2006 office 12 , sort and filter by color ( count
color will be better), fantastic for a profane like me.

No guarantees that it will do this until it is actually released, and even
if it lets you count colours, that doesn't mean you can count conditionally
formatted colours.


I'm sure there must be a way to condense this, but my brain's pretty fried
at the moment, so try this:-

=SUMPRODUCT((($D$3:$D$400)=($AL$3:$AR$400))+(($E$3:$E$400)=($AL$3:$AR$400))+(($F$3:$F$400)=($AL$3:$AR$400)))

This should count any cell in cells Dx Ex Fx that have values that appear in
the corresponding rows ALx:ARx

Regards
Ken.................

 

[Marco]

I'm sure there must be a way to condense this, but my brain's pretty fried
at the moment, so try this:-

=SUMPRODUCT((($D$3:$D$400)=($AL$3:$AR$400))+(($E$3:$E$400)=($AL$3:$AR$400))+(($F$3:$F$400)=($AL$3:$AR$400)))

That formula does not give back the attended result.
It would be simpler to explain if i could attaching file.

 

[Ken Wright]

By all means mail it to me at ken dot wright at ntlworld dot com

Regards
Ken....................