Getting the current row for excel sum product function
 

I don't know if this is possible but what i need to do is count in a row the
number of cells that are not empty [=SumProduct((C$7:C$193<"")) easy enough]
and that are a specifit color. not so easy. I made a function that tests for
the specific color, TestColor(give cell here) but I don't know how to pass it
the cell row sumproduct is currently on. here is what i have [
=SUMPRODUCT((C$7:C$193<"")*(TestColor()) ] So i need to put the cell in test
color like so TestColor(C7) but it needs to change for each row SUMPODUCT is
on. is this possible or do i have to do everything in VBA? any help is
appreciated.

Getting the current row for excel sum product function
 

This seemed to work for me (I tested for a red cell color (colorindex = 3) -
change as needed).

=SUMPRODUCT((C$7:C$193<"")*(testcolor(C$7:C$193)) )


Function TestColor(Rng As Range) As Variant
Dim i As Long
Dim Temp() As Boolean

Application.Volatile True
ReDim Temp(1 To Rng.Cells.Count)
For i = 1 To Rng.Cells.Count
Temp(i) = (Rng.Cells(i).Interior.ColorIndex = 3)
Next i

TestColor = Application.Transpose(Temp)

End Function


"paulgallanter" wrote:

I don't know if this is possible but what i need to do is count in a row the
number of cells that are not empty [=SumProduct((C$7:C$193<"")) easy enough]
and that are a specifit color. not so easy. I made a function that tests for
the specific color, TestColor(give cell here) but I don't know how to pass it
the cell row sumproduct is currently on. here is what i have [
=SUMPRODUCT((C$7:C$193<"")*(TestColor()) ] So i need to put the cell in test
color like so TestColor(C7) but it needs to change for each row SUMPODUCT is
on. is this possible or do i have to do everything in VBA? any help is
appreciated.