How to combine countcellsbycolor with sumproduct?

[bksparkz]

Good day All,
I am having trouble combining two formulas.

I have the following formula ....

=SUMPRODUCT(--(I2:I5000="YES"),--(J2:J5000=L2))

and I need to combine it with .....

=CountCellsByColor(F2:F5000,G17)

Basically I need to count all cells that are I2:I5000="YES" and J2:J5000=L2 and F2:F5000 with a color of G17

Thank You

 

[adam087]

Apologies, I'm not familiar with CountCellsByColor - what's the code within that?
I'm guessing though that you'll need a function that returns the cell colour and then use this as part of a comparison in the SUMPRODUCT. But if you post the CCBC code I'm sure we can adapt...

/AJ

 

[bksparkz]

The following is the function in which I used for the CCBC

Function</SPAN> GetCellColor(xlRange As</SPAN> Range) </SPAN>
Dim</SPAN> indRow, indColumn As</SPAN> Long</SPAN>
Dim</SPAN> arResults() </SPAN>

Application.Volatile </SPAN>

If</SPAN> xlRange Is</SPAN> Nothing</SPAN> Then</SPAN>
Set</SPAN> xlRange = Application.ThisCell </SPAN>
End</SPAN> If</SPAN>

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

Function</SPAN> GetCellFontColor(xlRange As</SPAN> Range) </SPAN>
Dim</SPAN> indRow, indColumn As</SPAN> Long</SPAN>
Dim</SPAN> arResults() </SPAN>

Application.Volatile </SPAN>

If</SPAN> xlRange Is</SPAN> Nothing</SPAN> Then</SPAN>
Set</SPAN> xlRange = Application.ThisCell </SPAN>
End</SPAN> If</SPAN>

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

End</SPAN> Function</SPAN>

Function</SPAN> CountCellsByColor(rData As</SPAN> Range, cellRefColor As</SPAN> Range) As</SPAN> Long</SPAN>
Dim</SPAN> indRefColor As</SPAN> Long</SPAN>
Dim</SPAN> cellCurrent As</SPAN> Range </SPAN>
Dim</SPAN> cntRes As</SPAN> Long</SPAN>

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

CountCellsByColor = cntRes </SPAN>
End</SPAN> Function</SPAN>

Function</SPAN> SumCellsByColor(rData As</SPAN> Range, cellRefColor As</SPAN> Range) </SPAN>
Dim</SPAN> indRefColor As</SPAN> Long</SPAN>
Dim</SPAN> cellCurrent As</SPAN> Range </SPAN>
Dim</SPAN> sumRes </SPAN>

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

SumCellsByColor = sumRes </SPAN>
End</SPAN> Function</SPAN>

Function</SPAN> CountCellsByFontColor(rData As</SPAN> Range, cellRefColor As</SPAN> Range) As</SPAN> Long</SPAN>
Dim</SPAN> indRefColor As</SPAN> Long</SPAN>
Dim</SPAN> cellCurrent As</SPAN> Range </SPAN>
Dim</SPAN> cntRes As</SPAN> Long</SPAN>

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

CountCellsByFontColor = cntRes </SPAN>
End</SPAN> Function</SPAN>

Function</SPAN> SumCellsByFontColor(rData As</SPAN> Range, cellRefColor As</SPAN> Range) </SPAN>
Dim</SPAN> indRefColor As</SPAN> Long</SPAN>
Dim</SPAN> cellCurrent As</SPAN> Range </SPAN>
Dim</SPAN> sumRes </SPAN>

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

SumCellsByFontColor = sumRes </SPAN>
End</SPAN> Function</SPAN>

 

[adam087]

OK cool. We can use the GetCellColor formula. I've mocked up the below and you can change the references as you need...

Excel 2010
	A	B	C
1	Yes or No	Match L2	Coloured
2	Yes	1
3	Yes	2
4	Yes	3
5	Yes	1
6	Yes	2
7	Yes	1
8	Yes	1
9	No	2
10	No	1
11	No	1
12
13	1	3	<-- Result
Sheet1

Cell Formulas
Range	Formula
B13	{=SUM(($A$2:$A$11="Yes")*($B$2:$B$11=A13)*(GetCellColor($C$2:$C$11)=GetCellColor(A13)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.


/AJ

 

[bksparkz]

Thank you for all your help. The formula you gave me made it work.

Thank You

 

[adam087]

Awesome. Thanks for the feedback.

/AJ