View Full Version : How to count number of red color in a row
vipulhumein
07-03-2013, 12:35 PM
i have a sheet in which many columns have conditional formatting , i want formula to count number of red background cell in a row
Note :- i am using excel 2007
thanks in advance
Regards
Vipul Jain
Kenneth Hobs
07-03-2013, 12:40 PM
Conditional formatting is not the best route. Use the formula condition for red, and iterate through the cells.
rollis13
07-03-2013, 01:30 PM
Also, have a look at this: http://www.cpearson.com/excel/cfcolors.htm
vipulhumein
07-03-2013, 09:39 PM
hello rollis & kennethh r u...
thanks rollis again for replying my query..
cpearson site is not helpful i have already tried it...
in my sheet there are many columns in which different conditional formatting is applied...
i only want that when the cell background color changes to red then count the no. of cell background with red in a row..
please give the proper solution only for red color
regards
vipul jain
rollis13
07-04-2013, 01:32 AM
If the Conditional Formating is the same for all the cells as in my attached example maybe what you ask could be done, elsewise, it looks like an impossible task as CPearson said, "Unfortunately, the Color and ColorIndex properties of a Range don't return the color of a cell that is displayed if Conditional formatting is applied to the cell. Nor does it allow you to determine whether a conditional format is currently in effect for a cell."
vipulhumein
07-04-2013, 08:35 AM
hello rollis
the file which u have attached had a formula countofcf but the formula does not works why... i dont understand the attached file how it works and how it is helpful to me..
please help me out i just want that how the red color background generated from conditional formatting should be counted
Regards
Vipul Jain
rollis13
07-04-2013, 09:01 AM
You need to copy to your book the CPearson's macros I placed in the code module of the my attached example.
Kenneth Hobs
07-04-2013, 09:04 AM
IF you fail to take my advice, I don't know how else to help you.
To get the best help, it is often necessary to provide a short example file.
vipulhumein
07-05-2013, 12:24 AM
I kenneth and rollis
Thanks for the quick feedback .
I have around 30 column in which conditional formatting is there with different condition in different column.
so please help me out.. my excel file is already become slow..as there are many sheet which have conditional formatting
please solve my query.
do u have any formula which only count red color or only count cells figure which are red in background.
Regards
Vipul Jain
rollis13
07-05-2013, 12:39 AM
Please fully understand: CPearson said, "Unfortunately, the Color and ColorIndex properties of a Range don't return the color of a cell that is displayed if Conditional formatting is applied to the cell. Nor does it allow you to determine whether a conditional format is currently in effect for a cell.".
This is his get around but works only if all the cells to be counted are CF the same manner: http://www.cpearson.com/excel/cfcolors.htm (http://www.cpearson.com/excel/cfcolors.htm)
vipulhumein
07-05-2013, 01:04 AM
hello rollis
This means i cant count the cell with red background color. Right
then how should i solve my query.
see i need this because i have made an automatic email received if there is any red background colored cell
so for that i only want to count red background color
Regards
Vipul Jain
rollis13
07-05-2013, 01:12 AM
It is easy to count background coloured cells only if coloured manually, not CF.
As already asked by Kenneth Hobs, a dummy sample (of the original structure) could be of some help.
vipulhumein
07-05-2013, 01:21 AM
sample u can use is the file which u have already attached "ex Count cells with condition colour.xls" there also the formula doesnt works
hey rollis means its impossible to count cell background color with cf
so what is the best solution for me to work please suggest me..as i have already made each and every thing for only this my work is pending
regards
Vipul Jain
rollis13
07-05-2013, 01:35 AM
My attached example is still working perfectly, in column J it counts the red cells in each row. These cells are red because the CF rule is: "greater than 20000".
Please attach true file elsewise no one can be of some help, or at least, say that what you ask cannot be done.
vipulhumein
07-05-2013, 10:10 AM
hi rollis the file which u have attached had a formula countofcf in column H,I,J,K results in "#value" why??? it does not count
rollis13
07-05-2013, 01:23 PM
My example with CPearson's code is fully working with my Excel 2003. Sorry can't test it with newer versions of Excel.
Aussiebear
07-05-2013, 03:49 PM
hi rollis the file which u have attached had a formula countofcf in column H,I,J,K results in "#value" why??? it does not count
Please attach a sample workbook. To do so, click on "Go Advanced", scroll down to "Manage Attachments" and follow the prompts from there. Its very hard to assist someone when we cannot see the workbook you are using.
Please also indicate which version of Excel you are using.
vipulhumein
07-05-2013, 10:08 PM
10225please see the attachment and run the file in excel 2007 ...i am using excel 2007 version...
in excel 2007 i think count for red color background is not working...
is there any other way to count in excel 2007
regards
vipul jain
Aussiebear
07-05-2013, 10:30 PM
Your CountOfCF formula is not able to count anything because you haven't asked it what it is that you want to count.
=CountOfCF($A2:$F2,1)
You named the range to count from "$A2: $F2", and you told Excel the result is to be true "1", but are you actually counting?
vipulhumein
07-06-2013, 12:30 AM
sorry aussiebear but i did not understand what u want to convey me..
as i dont know much in vba
do u know how to count red background color generated from conditional formatting if yes then please help me out
Regards
Vipul Jain
Aussiebear
07-06-2013, 02:37 AM
If you wish to count something, you need to do the following
CountOfCF( What to Count, The Range to count in, True or False)
You haven't told Excel what it is that you want to count.
vipulhumein
07-06-2013, 03:59 AM
thanks aussiebear
for such a quick feedback
if possible please give me the proper vba to count of red color in excel 2007 version
regards
vipul jain
Aussiebear
07-06-2013, 04:47 AM
What is the color index number of the cell you wish to count?
vipulhumein
07-06-2013, 05:37 AM
only red color background generated from conditional formatting i wish to count
Regards
vipul jain
Kenneth Hobs
07-06-2013, 12:37 PM
=COUNTIF(A2:F2,">20000")
shrivallabha
07-07-2013, 04:58 AM
=COUNTIF(A2:F2,">20000")
:clap:
rollis13
07-07-2013, 08:48 AM
If you wish to count something, you need to do the following
CountOfCF( What to Count, The Range to count in, True or False)
You haven't told Excel what it is that you want to count.The exact syntax is: CountOfCF( Range to count in, Rule to consider) 1-3 because Excel 2003 can have only 3 rules of CF (-1 for all rules).
vipulhumein
07-07-2013, 11:11 AM
hey rollis please help me how to count only red color background in excel 2007
or else please suggest me what else i can do to get solution without modifying my sheet
Redards
Vipul jain
vipulhumein
07-07-2013, 11:15 AM
hey rollis please help me how to count only red color background in excel 2007
or else please suggest me what else i can do to get solution without modifying my sheet
Redards
Vipul jain
rollis13
07-07-2013, 12:30 PM
The solution has already been given more times, please understand that if you don't attach a sample of your sheet (with dummy data but exact structure or at least one entire row where the count has to be done) it will be impossible for anyone to be of any help.
vipulhumein
07-08-2013, 10:06 PM
hello rollis for your ready reference please find the following attachment of my sheet. in the help sheet which has been attached there are many conditional formatting and many links from other file.
now please solve my query
Regards
Vipul jain
rollis13
07-10-2013, 01:23 PM
Sorry, but as said, I only have access to Excel 2003 so can't be of any other help.
Your task looks very difficult (if not impossible) to manage but I still hope you can get help from other members.
vipulhumein
07-10-2013, 10:43 PM
thanks for the feedback Rollis
Regards
Vipul Jain
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.