Can Excel count cells with formatting like colour fill?

[Arsie]

I am trying to enter data into cells and then do counts on the content as
well as the format.
e.g. count if the cell contains "B" or "A" or if the cell is yellow or red.
Is this possible? Am I making sense?


Arsiep

 

[Bob Phillips]

Arsiep,

Counting content is easy

=COUNTIF(A1:A100,"B") or
=COUNTIF(A1:A100,">10")


To count colour, you need a VBA function to get the colorindex in another
column, such as
Function ColorIndex(rng As Range)
If rng.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Else
ColorIndex = rng.Interior.ColorIndex
If ColorIndex = xlColorIndexNone Then
ColorIndex = 0
End If
End If
End Function
which returns a number and you can count the instances of these.

 

[Harlan Grove]

...
...

To count colour, you need a VBA function to get the colorindex in another
column, such as
Function ColorIndex(rng As Range)
If rng.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Else
ColorIndex = rng.Interior.ColorIndex
If ColorIndex = xlColorIndexNone Then
ColorIndex = 0
End If
End If
End Function
which returns a number and you can count the instances of these.

...

Yes, but it returns one color index value only, so the OP would need to populate
another range of cells with formulas using this in order to be able to count
colors in the original range. This sort of UDF could be implemented to return an
array of color indices, in which case SUMPRODUCT could be used for conditional
counting, and only one cell formula required.


Function ColorIndexAlt(rng As Range) As Variant
Dim r As Range, c As Range, i As Long, j As Long
Dim rv As Variant

If rng.Areas.Count > 1 Then
ColorIndexAlt = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then
rv = rng.Interior.ColorIndex

Else
rv = rng.Value
i = 0

For Each r In rng.Rows
i = i + 1
j = 0

For Each c In r.Cells
j = j + 1
rv(i, j) = c.Interior.ColorIndex
Next c

Next r

End If

ColorIndexAlt = rv
End Function

 

[Bob Phillips]

Phew,

I got worried when I saw who had answered<g>.

You're absolutely right, and I agree that it is much better solution. I had
assumed that the OP would create the helper column, but as he is only
wabting to count them (or so he says), doing it in one hit is better, more
efficient, and even lower rent (win, win, win!).

Just in case the OP doesn't appreciate how to use it as Harlan states, he
would put something like
=SUMPRODUCT(--(ColorindexAlt(A1:A100)=43)) to test for the colour whose
colorindex is 43, or even
=SUMPRODUCT(--(ColorindexAlt(A1:A100)=ColorindexAlt(B1))) whwre B1 is set to
the colour to test against as Harlan's version still allows a single cell
result.

Only criticism I would have of the solution is the nasty lack of indentation
<vbg>

Nice one HG. Will remember this for future reference.

Regards

Bob

...
..
..

Yes, but it returns one color index value only, so the OP would need to populate
another range of cells with formulas using this in order to be able to count
colors in the original range. This sort of UDF could be implemented to return an
array of color indices, in which case SUMPRODUCT could be used for conditional
counting, and only one cell formula required.


Function ColorIndexAlt(rng As Range) As Variant
Dim r As Range, c As Range, i As Long, j As Long
Dim rv As Variant

If rng.Areas.Count > 1 Then
ColorIndexAlt = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then
rv = rng.Interior.ColorIndex

Else
rv = rng.Value
i = 0

For Each r In rng.Rows
i = i + 1
j = 0

For Each c In r.Cells
j = j + 1
rv(i, j) = c.Interior.ColorIndex
Next c

Next r

End If

ColorIndexAlt = rv
End Function

--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something

else.