Checking colored cells using SUMPRODUCT

Question

I am trying to count the number of colored cells (which also satisfy another condition).

My cells are as follows:

My intention is to count the cells where there is a 'B' and where the adjacent cells are green in color.

I also write a function as follows:

    Function CheckColor(rng As Range) As Boolean
    If rng.Interior.ColorIndex = 43 Then
        CheckColor = True
    Else
        CheckColor = False
    End If
    End Function

I then use SUMPRODUCT function as follows:

=SUMPRODUCT(--(V40:V50="B");--CheckColor(W40:W50))

However, I get an error #VALUE!

UPDATE

I have modified my formula as follows:

Function CheckColor(rng As Range) As Variant
Dim arr As Variant
Dim n As Integer
ReDim arr(0 To rng.Count - 1) As Variant
n = 0
For Each cell In rng
    If cell.Interior.ColorIndex <> 43 Then
        bl = False
    Else
        bl = True
    End If
    arr(n) = bl
    n = n + 1
Next cell
CheckColor = arr
End Function

And I use the formula as follows:

=SUMPRODUCT((V40:V50="B")*CheckColor(W40:W50))

The answer I get is 6, which is wrong.

I googled and read somewhere that SUMPRODUCT can accommodate functions better — Echchama Nayak, Jul 26 '16 at 09:31
I think (but not sure) that your function is computing `CheckColor(W40:W50)` as a unique value, so doing `=SUMPRODUCT(--(V40:V50="B");0)` that will throw a `#Value` because of parameters of different sizes; have you the ability to try step by step formula computation ? — NatNgs, Jul 26 '16 at 09:43
Oh! I understand now. But I am still learning VBA so I am not sure how to achieve it — Echchama Nayak, Jul 26 '16 at 09:47
maybe `= SUMPRODUCT( ( V40:V50 = "B" ) * CheckColor( W40:W50 ) )` ? — Slai, Jul 26 '16 at 11:00
@Slai You are right. But I need to change my function to return an array of boolean values — Echchama Nayak, Jul 26 '16 at 11:08
then maybe array formula like `{= ( V40:V50 = "B" ) * CheckColor( W40:W50 ) }` — Slai, Jul 26 '16 at 11:10

Slai · Accepted Answer · 2016-07-26T12:23:49.277

2

The arrays for column ranges are a bit different Variant(1 To 11, 1 To 1)

Function CheckColor(rng As Range)
    Dim arr()
    ReDim arr(1 To rng.Count, 1 To 1) 
    ' arr = rng.Value2   ' arr Type in the Locals window shows as Variant(1 To 11, 1 To 1)
    For i = 1 To rng.Cells.Count
        arr(i, 1) = rng.Cells(i, 1).Interior.ColorIndex = 43
    Next i
    CheckColor = arr
End Function

edited Jul 26 '16 at 12:23

answered Jul 26 '16 at 11:55

Slai

22,144
5
45
53

I guess you figured it out. This will work only with the first column of the range passed. For example for `A3:C3` it will check only the cells in `A3:A3` – Slai Jul 26 '16 at 12:22

score 0 · Answer 2 · edited May 23 '17 at 12:08

You can do this without VBA, but you'll need a 'helper' column.

Create a named range with the name CellColour and the formula =GET.CELL(63,Sheet1!$B1)

Using your example (assuming it starts in cell A1), enter this formula in cells C1:C11: =CellColour. By the looks of your screen shot it should return 43 for green.

You can then use this formula to count column A with green in column B: =COUNTIFS($A$1:$A$11,"B",$C$1:$C$11,43)

Background colour: How to count cells in a range with a value less than another cell in excel?

Font colour: Excel formula to get cell color

thentt · Answer 3 · 2016-07-26T12:24:23.183

Edit, correction

In the function, you give the funtion a range and compare it to a ColorIndex. That means you ask if the whole range have the colorindex not the cells between.

What you do would work if Excel automaticly reference the right cells and compare them to the value, but i wouldn't do that for you.

So there are different ways you actually can manage this. First you do it without vba like Darren Bartrup-Cook meantion, you can do it with a helper column and write it like

 =If(V40="B";CheckColor(W40)

And count the with Countif the true values or you write it in VBA but then you need to loop trough the cells one by one like this:

For x = 1 to 50 
  If Cells(x,10).Value = "b" AND Cells(x,11).ColorIndex = 43 Then
       counter = counter + 1
Next x
Endif

Checking colored cells using SUMPRODUCT

3 Answers3