User Profile
Arturo89
Copper Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: sum by color when colors are set by conditional formatting
Jamil 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. Arturo6.1KViews0likes1Comment
Recent Blog Articles
No content to show