Excel: can you count coloured cells which are non blank?

[kennysarmy]

I'm struggling to get anything I've found on the internet to work :/

 

I have a row of data, some cells are formatted green and have a "1" or nothing in them, I want to count just the green cells which are empty in the row.

 

Any ideas?

[pooley]

Would the COUNTBLANK function not work for this? If you're looking to count only blank + green coloured cells then you probably need to use a macro or VBA code.

[6Foot2]

This page claims to have the solution:

 

Link: Count Colored Non-Blank Cells - Microsoft Community

 

5 people voted to say it was helpful.

[Marci]

What condition / formula makes the cell green? Probably need the formula for that combined with and(len(cell)=0,) to calculate it without needing to directly reference the cell color.

[kennysarmy]

This page claims to have the solution:

 

Link: Count Colored Non-Blank Cells - Microsoft Community

 

5 people voted to say it was helpful.

 

That works to count the cells which are a specific colour, but I can't work out how to amend it to only count cells which are a specific colour and empty or blank :/

[kennysarmy]

Guess I could do two separate formulas and take one number from the other - I'll play about with this but looks promising.

[andy_b]

Len© > 0 is checking for cells with some contents.

 

could make Len© = 0 ?

[Ditto]

Is this a formula you need the answer in a cell, to be updated as the sheet updates? Or would interactive use of the sheet to get the answer be sufficient. If the latter, remember 'Filter by colour' and then filter for blanks. The answer will be in the extreme lower left of the screen, where the filter details are located.

[howartp]

https://www.myexcelonline.com/blog/excel-if-cell-color-is-green/

 

Formula to count/process colour of cells.