I have a table (A1:A100) with numbers from 1 to 50 with three different colors

In cell B1:
i´d like to COUNT how many times the number 10 apears in RED

In cell B2:
i´d like to COUNT how many times the number 10 apears in BLUE

In cell B3:
i´d like to COUNT how many times the number 10 apears in GREEN

im a new user, so i couldnt make work with the solution suggested in my last
topic... a guy suggested the site
http://www.xldynamic.com/source/xld.ColourCounter.html
I apreciate everyone´s help! Please teach me step by step! thanks again!

At which point are you needing help?

Copying the code from Bob's site to a general module in your workbook?

Using the formulas found on Bob's site in your worksheet?

Are the cells colored due to Conditional Formatting?


Gord Dibben MS Excel MVP

On Mon, 1 May 2006 11:00:02 -0700, Fecozisk
wrote:

I have a table (A1:A100) with numbers from 1 to 50 with three different colors

In cell B1:
i´d like to COUNT how many times the number 10 apears in RED

In cell B2:
i´d like to COUNT how many times the number 10 apears in BLUE

In cell B3:
i´d like to COUNT how many times the number 10 apears in GREEN

im a new user, so i couldnt make work with the solution suggested in my last
topic... a guy suggested the site
http://www.xldynamic.com/source/xld.ColourCounter.html
I apreciate everyone´s help! Please teach me step by step! thanks again!

I was sucessfull copying the codes to a general module with no problem.

ater copying and paste the codes I iserted in a cell:
=SUMPRODUCT(--(ColorIndex(A1:A100)=3))
and this worked fine.

but when I inserted:
=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3))
that should counts all cells with red text within the range A1:A100 ir didnt
work... It focused a problem on A100,TRUE
So i had problems when using TEXT colors, and no BACKGROUND colors
Nothing is working when using TRUE!

I also wasnt able to use CONTIF (is it necessary? can I substitute countif
by sumproduct in my situation??). Finally, i also wasnt sucessful Using the
formulas found on Bob's site in my worksheet...

ps: My excel is in PORTUGUESE, so i changed SUMPRODUCT to SOMARPRODUTO,
which is the relative... Ive tried changing TRUE to its relative in
portuguese but it didnt work...

thank you

Could it be that your red didn't have a colorindex of 3?

I copied all 4 functions from Bob's site and both your formulas worked ok for
me.



Fecozisk wrote:

I was sucessfull copying the codes to a general module with no problem.

ater copying and paste the codes I iserted in a cell:
=SUMPRODUCT(--(ColorIndex(A1:A100)=3))
and this worked fine.

but when I inserted:
=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3))
that should counts all cells with red text within the range A1:A100 ir didnt
work... It focused a problem on A100,TRUE
So i had problems when using TEXT colors, and no BACKGROUND colors
Nothing is working when using TRUE!

I also wasnt able to use CONTIF (is it necessary? can I substitute countif
by sumproduct in my situation??). Finally, i also wasnt sucessful Using the
formulas found on Bob's site in my worksheet...

ps: My excel is in PORTUGUESE, so i changed SUMPRODUCT to SOMARPRODUTO,
which is the relative... Ive tried changing TRUE to its relative in
portuguese but it didnt work...

thank you

--

Dave Peterson