CONTIF DEPENDING ON TEXT/NUMBER COLOR

[Fecozisk]

Hi everyone!
I'd like to use contif not just for counting how many times a number
appears, but also for conting how many times the number 5 appeared in red,
for example.

I'd like to use five different colors

thanks everyone!

ps: i'm sorry, but im new user! please be patient and teach me step by
step!! Thanks a lot!

Fernando

 

[Miguel Zapico]

You need to use a custom function to return the colour of the cell, you can
find the function and also some examples on counting in this link:
http://www.cpearson.com/excel/colors.htm

Hope this helps,
Miguel

 

[JMB]

Bob Phillips also has a User Defined Function (UDF) that deals with colors
which may help you. You will need to paste the code into a visual basic
module, then you can use Bob's function as you would any other Excel function.

Bob's Colour Count:
http://www.xldynamic.com/source/xld.ColourCounter.html

Details on Sumproduct (which you'll most likely need for multiple condition
tests - also from Mr. Phillips site)
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Some information from David McRitchie's site to help you get started w/some
basics about macros and visual basic (if you are not already familiar)

http://web.archive.org/web/20031204...cid=/support/excel/content/vba101/default.asp

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Fecozisk]

i still have problems with this...
i could make everything work, but it didnt solved my problem.
I couldnt use CONTIF with any suggestions gaven above...
I need to use countif because i dont want just to sum or count red cells. I
want to count red cells that countain the TEXT "1f" on it. For example:

Imagine that in the interval A1:B10 i have 10 cells filled with the text
"1f", six written in red and 4 written in black
and 10 cells filled with "2f", seven written in red and 3 written in black

i need a formula that counts ONLY how many times the red "1f" appeared on
that interval
and another one that counts ONLY how many time the red "2f" appeared...
and etc...

thanls a lot guys!
Fernando

 

[Fecozisk]

the solution was under my nose!!!!!

=SUMPRODUCT(--(ColorIndex(A1:A5)=3)*(A1:A5="1f"))

THANKS A LOT!
now my problem is another... but for another topic!

 

[David McRitchie]

Glad you found your answer in the SUMPRODUCT Worksheet Function,
and wish I'd finished looking at the thread first. But to show how
complicated this can be I'll still post this.

When you indicate red cells, assume you mean interior color
and not font color. Except that you say Red 1f and a
Red 2f -- kind of sounds like Font.

Are you formatting the interior color, or the font color
Are you formatting the cell with format (Format, cells, pattern or font tab),
with a format condition (Format, cells, number or custom)
or with conditional formatting.
Without that information it is difficult to help you select a solution.

If you used Conditional Formatting to color the cell, they you should
use the same formulas as it is a lot more complicated to include
C.F. testing in the macros. And as far as I know Chip has not accepted
some adjustments to his Conditional Formatting color macros.

You would, of course, be in a lot better position, if you can use Worksheet Functions
instead of relying on macros or addins to figure out a color that could be calculated.

 

[JMB]

You're most welcome.