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

[LaurenHancy]

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)


[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]01-Jan[/TD]
[TD="class: xl66"]02-Jan[/TD]
[TD="class: xl66"]03-Jan[/TD]
[TD="class: xl66"]04-Jan[/TD]
[TD="class: xl66"]05-Jan[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl67"]David[/TD]
[TD="class: xl65"]availibility[/TD]
[TD="class: xl65"]*Red*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Lates
[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Red*[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl67"]Kelly[/TD]
[TD="class: xl65"]availibility[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Red*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Lates
[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Red*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="class: xl67"]Jane[/TD]
[TD="class: xl65"]availibility[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Lates
[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for you help.

L xx

 

[James006]

Great ...

For the Monthly Recap you want to create ... in which cells are you going to type : Jan Feb Mar etc ... ???

 

[James006]

Since you seem to have 50 rows of data ...

made the assumption your recap would be in Row 56 ...

Below is your revised UDF

Function CountCellsByInteriorColorWithConditions(rData As Range, cellRefColor As Range, sMonth As Range) As Long
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                               HOW TO USE THIS FUNCTION
' =CountCellsByInteriorColorWithConditions($C2:$JC2,$B$34,C$56)
' $C2:$JC2  the reference row
' $B$34 the cell which contains your red color
' C$56 the cell which contains the month...i.e Jan or Feb or Mar ...
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  
  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
          ' Added Month Condition '''''''''''''''''''''''''''''''''''''''''''''''''''''
          If indRefColor = cellCurrent.Interior.Color Then
              If Month(Cells(1, cellCurrent.Column)) = Month(DateValue("01-" & sMonth.Value & "-2019")) Then cntRes = cntRes + 1
          End If
      Next cellCurrent
 
    CountCellsByInteriorColorWithConditions = cntRes
End Function

Hope this will help

 

[LaurenHancy]

The months recap will be in a new sheet, E6:O6, these will be Jan, Feb, Mar etc

Taken from this data C1:ND1. date range from 01/01/19 to 31/12/19 like the below format.

[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]01-Jan[/TD]
[TD="class: xl66"]02-Jan[/TD]
[TD="class: xl66"]03-Jan[/TD]
[TD="class: xl66"]04-Jan[/TD]
[TD="class: xl66"]05-Jan[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl67"]David[/TD]
[TD="class: xl65"]availibility[/TD]
[TD="class: xl65"]*Red*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Lates
[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Red*[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl67"]Kelly[/TD]
[TD="class: xl65"]availibility[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Red*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Lates
[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Red*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="class: xl67"]Jane[/TD]
[TD="class: xl65"]availibility[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Lates
[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
</tbody>[/TABLE]

I hope I am making sense, so sorry If I'm being difficult here

 

[LaurenHancy]

Ive used your formula but it is just pulling through 0. This is what I have used:

=IFERROR(CountCellsByInteriorColorWithConditions('Daily Call KPI''s'!$C$7:$JC$7,$B20,E$6),"")

C7:JC7 - Cells with red and green cells
B20 - Red reference cell
E6 - Month reference

 

[James006]

Thanks for the infos ...

Talking about the Data ...

Since your are using an example with Row 7 ... Can you at least confirm all the Dates are located in Row 1 ... ???

And regarding the Color .. it is located on the same sheet as the Months with your Recaps ... right ..?

 

[LaurenHancy]

So the dates listed in sheet 1 (Daily Call KPI's) from 01/01/19 to 31/12/19 etc (C5:JC5)

Colour is located on the same sheet under each person's row:

James = (C7:JC7)
Tanya = (C10:JC10)
Katie = (C13:JC13)
etc

Sheet 2 (Monthly KPI stats) is where I am recapping the data into months. The months heading is Jan D6, Feb E6, etc,

Colour referance has now changed to F6 (Red). Just to make it even more confusing for you!!!

 

[LaurenHancy]

Colour is located in sheet 1, recaps on seet 2, colour referance is on sheet 2

 

[James006]

Have you tested :

=CountCellsByInteriorColorWithConditions('Daily Call KPI''s'!$C$2:$JC$2,$F6,D$6)