01-18-2019 03:46 PM
Good evening Mr. Jamil Mohammad,
I read your post and downloaded it. It works well.
But I would have a question of how to solve each line separately for each color.
Thank you for your reply and help in advance, a nice evening.
01-21-2019 03:53 AM
I will respond to your post due to the fact that i had the same challenge as you, and Jamil showed me the way around this.
If you check the previous post in which Jamil replied to me you will see that the CF range need to be exactly the row for which you need to make the count. you cannot use a range like A3:G16 because it won't work. The correct way to do it is to set like this: A3:G3. - for the first row.
And afterwards you need to use the format painter and to paste the CF formulas row by row. As a tip, if you want to make multiple formats with the format painter you can double click the format painter and it will remain active for all the pastes.
Jamil also made a video with this step in the post he replied to me.
i have attached the modified workbook
i hope it helps
01-27-2019 12:48 AM
thank you very much for your answer managed to solve my problem. I realized that there were a lot of conditional formatting and could not count the colors correctly.
Thank you very much for your help again.
02-05-2019 09:33 PM
The example that you helped me with worked like a charm but only for one conditional format formula applied for a range of cells.
Now i have a case in which i want to count all the CF cells in a row, but with multiple CF formulas.
I followed all the steps that you suggested (i hope i didn't missed something out).
I have attached the worksheet.
Thank you in advance
02-13-2019 01:31 PM
You did not properly set your conditional formatting in your file, that is why it was not working.
I set the conditional formatting in the attached file correctly and it works now.
plz see attached.
02-18-2019 12:32 PM
When I use the formula, it works rows in the column except for the 2nd row of data. It does not count that row. Has anyone hit this issue or found a way around it? It correctly counts all of the other rows so I know it is looking at the condition formatting.
02-19-2019 11:34 AM
03-29-2019 05:31 AM
Good morning Jamil! This thread has been SO helpful! I really appreciate it. Of course, since I'm joining I'm having trouble lol.
I added the UDF and made sure that the CF I'm using is based on formulas however I can't seem to get the results to be stable.
This is a fairly simple application and I'm feeling pretty dumb right now as I'm sure whatever it is I'm doing wrong is an easy fix.
Would you mind terribly taking a look? I tried a couple different formulas in the CF but even the straight-forward ones don't always end in the correct result. Sometimes the count is working and sometimes it isn't.
Any help you can give me would be much appreciated. This is making me nuts lol.
03-29-2019 08:23 AM
thanks for your kind words.
I saw your workbook and there are many issues with the CF formulas you set.
Double condition set for a single cell with contradicts each other.
so example in the cell E28 there are two formulas in CF and both of them contradicts each other.
another issue is that you set IF formulas to return test in CF which is not correct way to set formula.
for example if you want a condition to turn red if certain condition is met then you can simply use equal sign and there is no need for IF function, in fact IF function ruins it.
for example if you want to turn cell E28 to red if its value is greater than D28 then you can simply use =E28>D28 simply as this. CF will return True if condition is met and false if it is not met.
Please see attached your workbook.
I have added two more UDFs so that you can debug and find the problem.
The first UDF return the formula used in the CF for that cell
Function CondFormulaformula(myCell, Optional cond As Long = 1) As String
CondFormulaformula = myCell.FormatConditions(cond).Formula1
this second formula evaluates the formula used in CF and returns the evaluated value.
Function CondFormula(myCell, Optional cond As Long = 1) As String
CondFormula = Application.Evaluate(myCell.FormatConditions(cond).Formula1)
you can see in the column H which i highlighted in yellow. the evaluated CF from your formulas and they are not consistent. with the help of two addtional UDFs you can debug your CF formulas and get the correct result.
Hope this helps.
04-01-2019 01:53 PM
That worked perfectly! Thank you so very very much! One more question...Is there any way to have the cells that have no data in them remain uncolored? I was playing with the formulas but as I'm sure you've figured out by now I'm not the best at them. Everything else is working beautifully and I am grateful for what I've got but... :)
04-09-2019 05:53 AM
04-09-2019 08:09 AM
I want to count the number of yellow boxes as in the file below and output in the column "count color yellow"
Thank you so much!
04-09-2019 06:24 PM
I found this code to be used in my file but I want to switch from "Sub" to "Function". That is, I want the result of the yellow box displayed in the column count color yellow.
Thank you very much!!
04-11-2019 10:44 AM
Hi there! I tried adding the formula E$11="" and format is blank but then the COUNTConditionColorCells formula returns #Value! Any idea what I'm doing wrong?
04-12-2019 09:21 AM
04-12-2019 09:22 AM
04-15-2019 09:56 AM
Thank you. Your replies to this post have helped me so much!
I used your code and included Application.volatile. But, I can't get my spreadsheet to automatically calculate the number of coloured cells. It will only update the number of cells if I edit the cell in the formula bar. Is there a way I can set up a button on my spreadsheet that will force excel to calculate how many cells have been coloured?
04-15-2019 10:44 AM
04-15-2019 03:17 PM
04-15-2019 05:39 PM
04-20-2019 05:11 PM
@Jamil Mohammad Hi Jamil, I thank you for your work and effort to help.
But, it seems your code is not working here because I need to see conditional formatting changing colors at columns by the values of the cells and I need to count those color changes at rows direction, after that.
Do you think that it can be done?
04-30-2019 12:21 AM
I have try your VBA formula for sum by conditional formatting color, however formula return "0" result when data are in negative. I did conditionally formatted negative data by using ABS formula. I have staff attendance record which shows short hours by negative sign and excess hours by positive numbers. now I need to sum all short hours and excess hours first by highlighting through conditional format and then sum up those hours.
would you please help me to achieve this.
05-06-2019 04:32 PM
05-06-2019 08:18 PM
Thank you for response.
Sorry I did not make clear what I'm looking for. I do not want all negative to be sum only those cell value which is greater than -1.5 and less than -10.00 should be colored and sum and similarly all the cell value greater than positive 1.5 are to colored and sum.
I have done conditional formatting in attached file and try to apply your formula but its not working. Please help me out with this.
05-06-2019 08:32 PM
In cell CL2 put this formula and drag down =SUMPRODUCT((ABS(BP2:CK2)>1.5)*(ABS(BP2:CK2<10)))
and in CM2 put this formula and drag down =SUMPRODUCT((--(BP2:CK2>1.5)))
05-06-2019 11:09 PM
With this formula it is counting colored cell. For example when I copied the formula to CL2 result showing 22 which is count of color cell (or condition of greater than -1.5 and less than -10). I want sum of all color cell.
05-15-2019 09:05 AM
I am so VERY impressed with your expertise in MS Excel. I'm trying to do this very same task of counting cells based on CF color. I've attempted your code but get "NO-COLOR" as the output. I've attached a dummy file so you can see what's going on.
The only thing I can figure is that it's a different version of excel or I have a setting blocked somewhere.
Thank you so much for your kind consideration and help.
05-15-2019 10:54 AM
if you want to sum instead of count then use =SUMPRODUCT((ABS(BP2:CK2)>1.5)*(ABS(BP2:CK2<10)),BP2:CK2)
05-15-2019 10:58 AM
Thanks for your kind words.
I looked at the file you attached.
If you read my earlier posts, I have mentioned to other users with similar issues as stated below
Hope this helps.
05-17-2019 12:10 AM - edited 05-17-2019 12:12 AM
Hi Mr Jamil
I would like to thank you for this formula. It works great! However, I have a query. Please see attached file. I have a set of KPI’s for disciplines to meet. When I use your formula to count, ‘No of disciplines met by KPI’, it works. However, when I used your formula to count, ‘No of KPI's met by Discipline’, it doesn’t work. May I know what could be wrong? Your help is greatly appreciated :)
05-20-2019 03:01 AM
Thanks for sharing the code for counting colored cells in a conditionally formatted sheet. Just a quick question, "Can you please help me alter the code to make it count the colored cells in a specific row. This current code is default for range starting from cell A3. Say suppose I want to count the colored cells for range A4 to G4 in your sheet 'COUNTBYCOLOR.xls, it does not give me correct result."
05-20-2019 05:16 PM
It is because your CF is set C3:G3 and not C3:C5
so only C3:G3 works because your CF has this range.
05-20-2019 05:18 PM
if you want to count by row then your conditional formatting applied range should be also by that row and now the entire range. so if you want it to work on A4 to G4 then set up the conditional formatting range to be applied only on A4:G4
06-09-2019 11:07 PM
I know very little about VBA's.
I've got a very large s/sheet with data that I've just inherited. I need to add this months data to the master sheet (hystoric) and I'm trying to make the process easier and less time consuming (previously done manually). It's got columns with Customer Name, Address, Territory, Serial Number, Device Name, etc (columns A to O) and then approx 6900 rows of data. In my Serial Number column I've got a conditional format (column H) (format values where this formula is true =countif(if serial number is different to last month) - fill - red) and then I've got a conditional format in column K (format values where device name is different to last month - fill - blue). I've also got subtotals - count per territory number.
What I'm trying to achieve is to count all the non-coloured Serial Numbers (cells in H) and all the change in device name (non-coloured cells in K (less non-coloured cells in H)).
I've inserted your UDF in VBA. In H373 (first change in territory) I've typed =COUNTConditionColorCells and hit Enter and get the "#Value!" error. Not sure what I'm doing wrong, please help.
06-11-2019 02:00 PM
I'm trying this formula and I keep getting 0's. I'm not sure what I'm doing wrong and I'm wondering if you can help. See my data.
06-19-2019 04:18 AM
If you read my earlier posts, you will see that I mentioned UDF only works if CF is set using formula not build it conditions.
I changed the CF in the attached file as an example and see it works.
06-27-2019 07:56 AM
06-27-2019 07:56 AM
Hi Jamil, trying to use the count feature on conditional formatted cells, but the count is not accurate. My range of cells are restricted to 1 row at a time of cell highlighted in blue, but it appears to give the same result, regardless of the range provided. The spreadsheet has been attached. Any thought?
07-01-2019 12:06 PM
07-01-2019 12:06 PM
Hi @Jamil Mohammad ,
I have a similar question, however, when I applied the codes it only counts the cell by column, which I also need it to be counted by row. I wonder if it is because each column has a different condition formatting criteria. Could you please help? Please see the attached file.
07-01-2019 01:15 PM
07-01-2019 01:15 PM
Your CF range was vertical while the formula range was horizontal. so i changed your CF range to horizontal and it works now. see attached.
07-01-2019 01:17 PM
Hi, please read my earlier posts. the UDF do not work if the CF is set using built-in conditions. UDF works only when you set conditions with formula. "Use a formula to determine which cell to format"
if you set your CF using formula, then it works.
07-07-2019 03:26 PM
Hi could you please check my file, i can't make it work, it gives me the error:
English is not my first language, so i tried to make it work reading the posts here. Thanks if you can take a look into my file.
07-17-2019 11:44 AM - edited 07-17-2019 11:47 AM
Hi, I try your code and i still cant count color in my workbook. keep saying no colour, eventough its already the same color
can you take a look at my file? really appreciate it
is it because I am using excel 2013?
07-18-2019 11:17 AM
07-19-2019 09:50 AM
I've been using your UDF for counting conditional cells and it has been working great. I was wondering if there is a way to modify the code so that when I filter my data in a table, the totals will update automatically without counting the hidden rows.
07-29-2019 10:19 AM
09-02-2019 12:23 AM
I have a rather large excel file with 480 columns and 1451 rows. I currently have conditional formatting set up to find - and color - the largest value in each row. What I need to know is the count of row maximums in each column. I see that your solution only works when the count function and conditional formatting are applied to the same range. I already know how many I have per row - 1. Is there any way to count conditional formatting when the ranges don't match?
09-04-2019 08:10 AM
09-05-2019 02:47 PM
No, I'm trying to count when the condition is met, however, my conditional formatting runs across rows. I need to count instances when conditional formatting is matched within columns. As you had previously mentioned to someone else, your tool for counting conditional formatting only works when the range and the area being counted match. My question is: is there a workaround for when the conditional formattting range and the range being summed do not match?
FYI the same condition is being tested for within each row for each row within the summed columns.
09-05-2019 02:51 PM - edited 09-05-2019 03:20 PM
I know it is possible to sort/filter via color and I could do that - and manually count - for each of my 480 columns; but since my data will change frequently over the course of the project, I'd prefer not to manually count 480 columns dozens, possibly hundreds, of times. I would much prefer to have a readout listed for each column.