Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONTIF DEPENDING ON TEXT/NUMBER COLOR
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONTIF DEPENDING ON TEXT/NUMBER COLOR
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 "Fecozisk" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONTIF DEPENDING ON TEXT/NUMBER COLOR
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/200312040...01/default.asp http://www.mvps.org/dmcritchie/excel/getstarted.htm "Fecozisk" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONTIF DEPENDING ON TEXT/NUMBER COLOR
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONTIF DEPENDING ON TEXT/NUMBER COLOR
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONTIF DEPENDING ON TEXT/NUMBER COLOR
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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Fecozisk" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONTIF DEPENDING ON TEXT/NUMBER COLOR
You're most welcome.
"Fecozisk" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem not solved... CONT.IF depending on the text color | Excel Discussion (Misc queries) | |||
Various Fill Color depending on variouse versions? | Excel Discussion (Misc queries) | |||
Color a cell and a value is automatically assigned to that color. | Excel Worksheet Functions | |||
Changing cell or text color depending on week number | Excel Discussion (Misc queries) | |||
How do I get the font color to change automatically depending on | Excel Discussion (Misc queries) |