- last edited on
i have a column of numbers that are color coded (to represent a specific mfg department) and I need to total the values by color. Meaning I need to total all the values that have the same background color.
I have done an exhaustive search online and was able to quickly find a way to sum by color, unfortunately it only worked for cells whose color was set manually (NOT using conditional formatting). I have found a few references to VB code that should provide the functionality but I can't get any of them to run (except for the one that works for manually set colors).
I have control of the data that I'm trying to sum. is there another method to "tag" values? I thought of adding a letter prefix, but coulnd't find any way to sum a column of numbers that are contained in text strings.
Any help would be greatly appreciated!!
I'm using Office 365 (excel 2016) on a windows 10 machine
02-21-2019 08:36 AM
Hi @Jamil Mohammad, firstly thanks for taking the time to help and reply to so many members.
I have read through the whole thread and cannot find the solution to my issue. Hope that you are able to help.
I am trying to use your count UDF to count the number of cells conditionally formatted either red/orange/green/white as per the bottom of the table. My CF are all defined by a function, and ranges seem to match up, colours are the same as I have inputted the RGB values. I am still getting the #VALUE error in rows 136 through 138, and a 0 count in row 139. Maybe my CF rules are too complex?
02-21-2019 09:32 AM
Thanks for your kind words.
I looked at your file. the CFs are not consistent with the range used in the UDF.
for example the red color range in CF is =$AP$135,$H$4:$BK$134 double while in the UDF it is only H$4:$H$134 if you read my previous messages, you will see that most of user miss this part.
I noticed that you you CF formulas are also having circular reference issue.
You can simplify your workbook by using helper column/cells and perhaps a formula solution to count is much more easier than the use of this UDF. I have given several examples in the earlier comments on this thread to other users.
Hope it helps.
02-24-2019 12:54 AM
Thank you for all that you have done in this thread. I've read through it all. Although some had similar issues, so far I haven't read one that's similar to mine.
Here's what I need... For each row, there are 27 cells with conditional formatting. Based on the conditional formatting (using formulas to determine the format), the cell will either turn (standard) green, yellow or red. At the very end up each row, I have three more columns: Required, Completed, % Completed. I already have the formulas for the Required and Percentage columns figured out. Now, I simply need the formula to count all cells that have the standard green fill for the Completed column. I tried what you have kindly offered on this thread, but it's not working for me. The two problems I'm facing are below.
Problem #1 - The UDF/formula does not count after 4 cells.
I don't know why this happens, but the result returns as #VALUE! if I try to do all 27 cells in a row, but once you edit the range down to four cells or less, it seems to work.
Problem #2 - The UDF/Formula seems to count all cells, instead of a specific color.
Along with problem #1, the formula seems to calculate any cell with any background color.
Let's take the following as an example: Formula =COUNTConditionColorCells(A2:C2,$E$2)
Cell E2 has a Standard Green fill. Cells in A2:C2 all have the background color of Standard Green, so the result is 3. Let's say I drag the formula down to row 3 now, which consists of all Standard Yellow. The formula *should* only count the cells with Standard Green fill ($E$2), right? In my case, it's still returning as 3.
Based off what I read in this thread, I imagine it something having to do with the conditional formatting being applied to nonconsecutive groups of cells. So, I went ahead and edited the document so I can apply each rule to one, consecutive group of cells. Unfortunately, this does not fix the problem.
There's really only one more thing that I can think of; however, I don't know if that's the main issue. There are some cells that do not contain a formula for conditional formatting. If you look at the spreadsheet, some of the cells have "NR" instead of a date. So the conditional formatting is set to "if contains." If this is the issue... what kind of formula would work to conditionally format a cell if it contains "NR"?
I've attached a dummy file of my original one. I would appreciate your help very much! I would like the formula to be applied to AH9:AH80.
02-25-2019 03:46 AM
I looked at your file.
There was a lot of issues in the CF range , CF formula and the range.
I have demonstrated in the attached workbook how the CF are now calculating correctly.
things i changed in the file are the followings
Removed the CFs that was not set using formulas.
removed CFs with the whole range instead of per row
added the AND operator to only apply if cell is not blank.
applied the CF on row F3 to AF3 and then used Paint brush to duplicate it for all other rows.
your Range contained mixed input numbers and text. so i replaced the NRs with blank
please see attached workbook.
03-04-2019 08:06 AM
I am trying to use your formula, and I keep getting zero count. Here is my file. I'm hoping that you can help me.
03-08-2019 09:58 AM
03-22-2019 12:59 AM
Thanks for all your good help here. I copied your COUNT VBA but there is problem hope you can help me with:
if the conditional formatting is done by simple formula like >50 e.g. it works.
But my conditional formatting is done with this formula: =AND(T3<=$K3,T3>=($K3-$L3+1)).
Now it doesn't work anymore.
Can you help out?
Thanks a million!
03-26-2019 09:16 AM
I tried your code but it returns "0"s. Could you be so kind and take a look at my sample file?
03-29-2019 08:28 AM
04-09-2019 08:03 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:26 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-10-2019 11:38 PM
Hi Jamil!@Jamil Mohammad
This is my conditional formatt rule for January, person 1:
(Person 1, ROW 7) =AND(C9="EL",C9<>"") ------- Applies to =$C$7:$Y$7
When I click the paint brush and paste to other rowns, the formula doesnt change, it just adds to the applies to box like the below:
(Person 2, ROW 11) =AND(C9="EL",C9<>"") --------Applies to =$C$7:$Y$7, $C$11:$Y$11
(Person 3, ROW 15) =AND(C9="EL",C9<>"") ------ Applies to =$C$7:$Y$7, $C$11:$Y$11, $C$15:$Y$15
And so on...
I need my conditional formatting to be:
(Person 2, ROW 11) =AND(C11="EL",C11<>"") --------Applies to =$C$11:$Y$11
This is my formula to average the coloured green cells with time values:
I have already completed 1 full row for 12 months for 1 person, this has taken me all night!! Are you able to advise why my rule is not following sequence when copying using the paintbrush as per the video you posted?
I appreciate your help with this, I am stuck in glue with this and I dont fancy completeing conditional formatting for 740 rows!!
04-12-2019 09:15 AM
04-12-2019 09:16 AM
04-25-2019 12:18 PM
Apologies in advance if this inquiry is in the wrong place or if you've answered this particular question before but I am having an issue that I am hoping you can help with. I have tried using your UDF and it works, but it doesn't appear to be exactly what I need. Currently, my sheet is set up in a way that if any cell in a column is filled in a certain range then another cell outside of that range will be filled with a color. I would like to count the cell that is filled, not the cell that has the info inputted. Does that make sense? I've attached my spreadsheet.
You have been so helpful with everyone on this thread and I hope you can assist me as well.
04-25-2019 02:25 PM - edited 04-25-2019 02:26 PM
Thanks for your kind words.
I looked at your file. You do not need to overkill it with UDF and too many repetitive conditional formatting rules.
You just need a simple formula like this one, I wrote for you.
First, I deleted all of the conditional formatting rules that had the pattern style in the range area C3:ND16.
Then I placed one single formula =IF(COUNTA(OFFSET(INDEX(C$18:C$114,MATCH($A3,$A$18:$A$114,0)),1,0,5,1))>0,1,0)
and copied down and right in all range up to C3:ND16.
then, I selected area C3:ND16 and right clicked and then clicked on "Format cells" and then "custom format" then used three semicolons as the custom format like this ;;;
click ok and it has hidden the content of the cells from visibility.
then I selected C3:ND16 range and created one single Conditional formatting rule =C3=1 and selected fill pattern style and clicked ok.
Saved it. and uploaded it also here.
you will see that this solution is much more simplified and robust.
please see attached workbook.
04-25-2019 03:50 PM - edited 04-25-2019 04:01 PM
Thank you so much for your quick reply! I've reviewed the file but I still have one question. How can I count all the filled cells in a row and tally them in the "Total Days" column? For example, in row 3, I'd like to keep a running total under "Total Days" of all the filled cells in that row. Is there a way to do that?