Countif where cells contain a colour or colours

[colinheslop1984]

Is there a way to conduct a countif function where it only counts the cells that contain a colour or counts all cells but deducts the number of cells containing a particular colour, grey for example.

ie in cell e19:f19 (merged cells) I want to count the number of cells that do not have the fill colour grey from range e8:f17.

Also in some instances cells may be merged, if this affects it, I need a workaround.

Any ideas would be appreciated

 

[KOKOSEK]

If colouring is from conditional formatting is very, very complicated (check here: http://cpearson.com/excel/CFColors.htm)
If colouring is 'hardcoded' it is possible by VBa (by identifying cell.interior.color)

 

[colinheslop1984]

Is there a way to conduct a countif function where it only counts the cells that contain a colour or counts all cells but deducts the number of cells containing a particular colour, grey for example.

ie in cell e19:f19 (merged cells) I want to count the number of cells that do not have the fill colour grey from range e8:f17.

Also in some instances cells may be merged, if this affects it, I need a workaround.

Any ideas would be appreciated

I need to do this without using the subtotal and filter solution

 

[James006]

If colouring is from conditional formatting is very, very complicated (check here: http://cpearson.com/excel/CFColors.htm)
If colouring is 'hardcoded' it is possible by VBa (by identifying cell.interior.color)

@ KOKOSEK

Excellent Comment ...!!!

 

[colinheslop1984]

If colouring is from conditional formatting is very, very complicated (check here: http://cpearson.com/excel/CFColors.htm)
If colouring is 'hardcoded' it is possible by VBa (by identifying cell.interior.color)

That is well beyond my capabilities lol.

What about counting cells that have text / or are not empty (taking away the need for the colour counting)

Again, some cells are merged in case this affects anything

 

[Special-K99]

FYI

Merged cells: I don't know why people shun merged cells without checking their locations.
If the merged cell values are linear (every n columns, every n rows) then a formula should be able to read
e.g. If there is data in merged cells at columns 1, 4, 7, 10 then

IF(MOD(COLUMN(),3)=1) identifies the columns that have data

 

[colinheslop1984]

Hi, thanks for trying but that doesn't work how I hoped it would.

The cells contain text, rather than values. Also the merged cells are not linear, they can be altered and some can be bigger than others, there is no logical pattern. However I am able to specify particular ranges.

For example I might only want to count c8:d19, then e8:f19, g8:h19 and so on, but some of the merged cells might only be within c:d, others could be e:h.

Any possible suggestions to do it with helper cells maybe?, maybe a true/false binary code type set up, if you know what I mean

 

[James006]

Un-Merge all your cells ... !!!

And Forget all your Headaches ... :wink:

As a matter of fact, merged cells should be BANNED in Excel ...

 

[ClaireS]

That is well beyond my capabilities lol.

What about counting cells that have text / or are not empty (taking away the need for the colour counting)

Again, some cells are merged in case this affects anything

For your example of a formula in (merged) E19:F19, and counting text cells in E8:F17, try this in E19:F19:
=COUNTIF(E8:F17,">""")

NB After the comma it's doublequote, greater-than, doublequote-doublequote (for null text string) and a final doublequote to finish the text expression.

Some thoughts:

1. Merged cells (although manageable if regularly spaced as @Special-K99 pointed out) do cause all sorts of difficulties. I blame Microsoft for putting "Merge and Center" so prominently on the home tab. You may occasionally need to merge cells vertically as there is no other way of doing it, but for horizontal "merges" I find that it is far preferable to open the font formatting dialog and, on the Alignment tab, use "Center across selection". Looks the same visually but means that each cell can still be selected individually.
2. What determines the grey fill? If you are using conditional formatting for that, could you use the same condition as the criteria argument in the COUNTIF function?

Hope this is of some use.

 

[KOKOSEK]

@ KOKOSEK
Excellent Comment ...!!!

Is it sarcasm?