SOLVED

How to count and sum "Condtional formatting" cells by color in Excel 2010?

Deleted
Not applicable

I installed few add-ins for counting "Conditional formatted" cells but all are generating an instant (one-time) numeric result by activating that command.

 

Also, I tried a code which gives an instant counting of 1 picked color without a total sum of all other colors -in my case 12 colors in a matrix. ("How to use the code to count colored cells and sum their values" - https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/ )

 
My question is: Is there any functional code (or other aproach) which can count and sum all cells by "Conditional formatted" colors, on one sheet, and that the generated result is linked through common formulas? That can be updated/refreshed/code run during the work process as a complete overview of colors of a matrix (and not by picking every color every time as I mentioned).
 
Thanks in advance!

 
181 Replies

@Jamil Mohammad 

 

Hi Jamil,

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."

Thanks

@emy_77 

 

It is because your CF is set C3:G3 and not C3:C5 

 

so only C3:G3 works because your CF has this range.

@sudeep_batabyal 

 

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

@Jamil Mohammad 

Hi There

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.

@Jamil Mohammad 

 

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. 

@CeliaT

Can you upload a dummy sample file?

@ekimble83 

 

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.

 

 

@Jamil Mohammad,

 

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? 

 

 

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. 

 

Thanks!!

@Deleted 

 

Hi,

 

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.

@vc337 

 

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.

@Jamil Mohammad 

Hi could you please check my file, i can't make it work, it gives me the error:
#VALOR!

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.

@Jamil Mohammad 

 

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?

Hi

your conditional formatting range and the range used inside the UDF are not the same, that is why it does not work. please read my earlier comments to other users with similar issue.

@Jamil Mohammad 

 

Hi Jamil,

 

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.

Hi Robin,

It is maybe possible to do that, but I do not have time to write the code. I suggest you open a new question in the forum.

@Jamil Mohammad 

 

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?

I could not understand your question.

If you are trying to count number of cells when condition is not matched, then you simply sort your data by color of "none" and then count.

@Jamil Mohammad 

 

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.

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.