How do I count the rows in a spreadsheet that contain cells that are highlighted?

[Diane Cuthbertson]

I have a massive spreadsheet that has various cells highlighted in yellow. I need a row count where there exists cells with that highlight.

 

[NARAYANK991]

Hi Diane ,

Can you clarify whether the yellow is the result of manual formatting of the cells or is it the result of conditional formatting ?

Narayan

 

[ThrottleWorks]

Hi,

Could you please try below method. This will give you count of cells which has Yellow background.
You can modify the formula as per your need.

Go to Formula manager.
Formulas --> Name Manager --> New
Paste this formula =GET.CELL(63,!A1)
I have named it as "GetCellColor".

Assuming your data is column A. Type this formula in B.
=GetCellColor
The formula will return numbers based on the cell color.
6 is for Yellow. Then take a countif of 6.

 

[NARAYANK991]

Hi Sachin ,

Please note that the function GET.CELL(63,...) does not consider CF cells.

Secondly , when ever there is a change in the colour of any cell , the user will have to press ALT CTRL F9 to ensure that the change is taken into account.

Narayan

 

[ThrottleWorks]

Thanks for the guidence Sir, I was not aware of this (CF) limitation. In future I will certainly keep this in mind while using (and suggesting).

P.S. - Will try to learn what works of CF too.

 

[ThrottleWorks]

Hi,
Just wanted to share what I tried. I am aware this is not the efficient way to do it.
Also, this does not work for CF cells.

Could you please share a sample workbook. If the color cells are result of CF then I think we can design a formula to get the count.

 

[nadjalil]

Dear Narayan Sir
if want to count cells which has yellow background which formula shell we use...

Nadeem

 

[Khalid NGO]

Hi Diane and Nadeem,

If you have used conditional formatting to fill background, than you can use the same CF formula in any helper column, it will return TRUE/FALSE.

Than apply simple =COUNTIF(YOUR_RANGE,TRUE)

See the sample.

Regards,