COUNTIF formula to look up text values and the colour of the cell

[twaccess]

Is there a way I can use a formula like COUNTIF to scan a large spreadsheet to return the number of occurrences of a value say 'XY' where the cell colour is also highlighted in Yellow ? At the same time, ignoring all the occurences where the cell isn't highlighted ?

Any help would be gratefully received.

Tdub

[MarvinP]

Hi twaccess,

Counting values in cells is easy but the color something is, is very hard. If you had the formula that colors these cells yellow and add that criteria to a CountIfS function you might succeed.

[D.Lovell]

You would have to create a custom fiction with vba to count cells by colour. It works by comparing them to the colour of another cell, so you would need to have an input cell where you set the background colour to the one you want to find. Google 'Excel count cells by colour'.

[Khalidngo]

Hi,

Little painful, but non-vba solution is...

Suppose your data in column B, add a helper column (e.g. column C) with any value (say 1) to your highlighted cell.
Now use:
=COUNTIFS(B3:B10,"XY",C3:C10,1)
adjust the range to suite.

Blessing.

[AliGW]

Hi twaccess,

Counting values in cells is easy but the color something is, is very hard. If you had the formula that colors these cells yellow and add that criteria to a CountIfS function you might succeed.

It's not hard at all if the colour was applied using conditional formatting, as in such cases, the CF formula can be incorporated into the counting formula. If it has been applied manually, then it's harder.

[Pepe Le Mokko]

It's not hard at all if the colour was applied using conditional formatting, as in such cases, the CF formula can be incorporated into the counting formula.

Isn't that what Marvin implied?

Hi twaccess,

If you had the formula that colors these cells yellow and add that criteria to a CountIfS function you might succeed.

[AliGW]

Isn't that what Marvin implied?

Implied, yes, but didn't actually say. I was providing clarity in case the OP was unable to infer what Marvin was implying.

[sandy666]

module1:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I don't see in original post: VBA is prohibited