# ColorCellsBy Colour plus 2 more matcing criteria HELP, SO STUCK!!! :)

#### LaurenHancy

##### Board Regular
Hi All,

Hope you are having a great Tuesday. I desperately need your help.

I need to count the number of red cells (data range in colour C2:JC2) per person depending on the the following criteria:

Name = Collum A
By Month, Jan, Feb etc = Row C1:YC1 (MM/DD/YYYY)
"Lates" = Collum B

B34 is the *red* cell referance

I have the following formula already but I cant seem to add the other criteria into this formula. It works but I have to select which cells to count for each name and month. There are many names to contend with.

=CountCellsByColor('Daily Call KPI''s'!\$C\$2:\$JC\$2,\$B34)

 A B C D E F G 1 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 2 David availibility *Red* *Green* *Green* *Green* *Green* 3 Lates *Green* *Green* *Green* *Red* 4 Kelly availibility *Green* *Red* *Green* *Green* *Green* 5 Lates *Green* *Red* *Green* 6 Jane availibility *Green* *Green* *Green* *Green* *Green* 7 Lates *Green* *Green* *Green* *Green*

<tbody>
</tbody>

Thank you for you help.

L xx

Last edited:

#### LaurenHancy

##### Board Regular
correction: data range where colours need to be counted would be in c2:JC50 etc

#### James006

##### Well-known Member
Hello,

Your UDF is a function which is supposed to produce what you are looking for ...

Haven' t you asked a very similar question not that long ago ... ???

https://www.mrexcel.com/forum/excel-questions/1091506-counting-coloured-cells-2-criteria.html

Code:
``````[COLOR=#333333]Function CountCellsByFontColor(rData As Range, rMonth As Range)
[/COLOR]' for example = CountCellsByFontColor(C2:JC502, C1) '''''''''
' in cell C1 the Month such as : January
Dim indRefColor As Long
Dim cellCurrent As Range
Dim countRes
Dim lMonth As Long

Application.Volatile
countRes = 0
indRefColor = vbRed    ' Color Code for Red
lMonth = Month(DateValue("01 " & rMonth.Value & " 2019"))

For Each cellCurrent In rData
If cellCurrent.Interior.Color = indRefColor Then
If Month(Cells(1, cellCurrent.Column)) = lMonth Then
countRes = countRes + 1
End If
End If
Next cellCurrent

CountCellsByFontColor = countRes
End Function``````

Last edited:

#### LaurenHancy

##### Board Regular
Hi James,

Thank you for your reply. Yes I did ask the question and I took code but I am stuck writing the formula, are you abel to tell me whats best to use? Thank you for your help, with both posts

#### LaurenHancy

##### Board Regular
Also I am counting cells by colour not font colour, do I just take font out?

#### LaurenHancy

##### Board Regular
Can somebody please fix my formula

=IFERROR((SUMPRODUCT('Daily Call KPI''s'!C4:Y4, E6)*('Daily Call KPI''s'!A6:A50, A7)*('Daily Call KPI''s'!B6:B50 = "Availibility"),(CountCellsByColor('Daily Call KPI''s'!\$C\$7:\$Y\$7,\$B20))

#### James006

##### Well-known Member
Hi Laura,

There are only two possible colors : either the Font Color Or the Interior Color ...

1. Which one are you using ?

2. If it is the Interior Color ... is it Manually OR the result of some Conditional Formatting ???

3. If it is Conditional Formatting ... What is the rule which generates the Interior Color ???

4. Could you post your current version of CountCellsByColor ...???

#### LaurenHancy

##### Board Regular
Code:
``````Function GetCellColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()

Application.Volatile

If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If

If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
Next
Next
GetCellColor = arResults
Else
GetCellColor = xlRange.Interior.Color
End If
End Function

Function GetCellFontColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()

Application.Volatile

If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If

If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color
Next
Next
GetCellFontColor = arResults
Else
GetCellFontColor = xlRange.Font.Color
End If

End Function

Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long

Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent

CountCellsByColor = cntRes
End Function

Function SumCellsByColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent

SumCellsByColor = sumRes
End Function

Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long

Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent

CountCellsByFontColor = cntRes
End Function

Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent

SumCellsByFontColor = sumRes
End Function``````
This is the code I have. I am using the interia colour and it is coloured red or green manually.

L

#### James006

##### Well-known Member
Thanks for the long list of UDFs ( User Defined Functions)

The one we need to concentrate on is the following

Code:
``````Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long

Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent

CountCellsByColor = cntRes
End Function``````
Now ... to correctly modify it ...

What are precisely the TWO conditions you need to add ...???

Last edited:

#### LaurenHancy

##### Board Regular
Oh sorry about that I am quite new to UDF's, VBA Macros and all that shazz!

The criteria i require is:

Name (A)
Category (B)
Date (Row 1)

Thank you for your help with this James, its much appreciated