Counting cells with a certain color and value

[garretthered]

I need help trying to get a count of cells that have a specific fill color and certain value. It would be like how many pink cells contain the value 3.

 

[63falcondude]

Short answer is that you cannot count cells based on color. If there is any logic as to why those cells are pink then we might be able to use that.

 

[Joe4]

Short answer is that you cannot count cells based on color.

Actually, you can, using VBA. As matter as fact, some people have created their own "CountByColor" functions.
See:
Excel Counting cells by interior color using VBA
Color Functions In Excel

 

[63falcondude]

Actually, you can, using VBA. As matter as fact, some people have created their own "CountByColor" functions.
See:
Excel Counting cells by interior color using VBA
Color Functions In Excel

Key word was "short". Sure it's possible but it isn't simple (at least not to me). If the OP wants to create a UDF then more power to them.

 

[Joe4]

Key word was "short". Sure it's possible but it isn't simple (at least not to me). If the OP wants to create a UDF then more power to them.

You should probably clarify that a bit in the future so it is clear that you are not saying it is not possible (because it can easily be interpretted that way).
Maybe something like "Short answer is that you cannot EASILY count cells based on color WITHOUT VBA."

The great thing about many of those pre-made UDFs is that you don't really need to write them yourself or even have to understand how they work in order to use them. Many are just "plug and play", in that you just drop the VBA code into a VBA module, and then you can use it like you would any other native Excel function.

 

[T. Valko]

It should be noted that basing calculations on formats is not a good practice.

Changing a cell format does not trigger a calculation so the formula may return incorrect results until some event does trigger a calculation.

This is noted in Chip Pearson's web page.

 

[garretthered]

I am using VBA. I have two sets of codes that I need help altering that i can combine them into one piece of code.

This one counts cells with a chosen fill color:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lcol As Long
Dim vResult
lcol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lcol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lcol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

And this one count cells with text and a specified fill color:
Sub CountColorValue()


Dim numbers As Long, lastrow As Long
Dim rng As Range, c As Range


'Find the last row
lastrow = Range("A" & Rows.Count).End(xlUp).Row
'Set the range you want to search through
Set rng = Range("A1:A" & lastrow)


'Iterate through each cell in the range
For Each c In rng
'If the interior color is 6 (standard yellow), not blank and not a number
If c.Interior.ColorIndex = "6" And c.Value <> "" And Not IsNumeric(c.Value) Then
'Add 1 to numbers
numbers = numbers + 1
End If
Next c


'Message box with the value of numbers, change to display
'however you'd like
MsgBox numbers


End Sub

 

[garretthered]

Just for reference I am creating a scheduling spreadsheet that uses colored cell for gender and a number value that corresponds to a specific work shift.

 

[garretthered]

Just for reference I am creating a scheduling spreadsheet that uses colored cell for gender and a number value that corresponds to a specific work shift.

https://1drv.ms/i/s!Am3S8a5scNQKlCrqfAQQwfMth6-E

 

[Joe4]

Unfortunately, I cannot see any of your images from my current location.
But assuming your second set of code is in working order, it looks like you should be able to just use your second block of code, with one minor change, i.e.
change

[COLOR=#333333]If c.Interior.ColorIndex = "6" And c.Value <> "" And Not IsNumeric(c.Value) Then
[/COLOR]

to

[COLOR=#333333]If c.Interior.ColorIndex = 6 And c.Value = 3 Then
[/COLOR]

If you want to count the cells with interior color index of 6, and a value of 3.