sum cells in a range that are color index = 3

[Darron Ross]

Thanks for you time and help


I'm trying to sum a set of number in a range(b5:b223) that
are only if the colorIndex = 3. I tried using an if
statement that used colorindex = 3 for the logical test.
But ran into syntax problems. Thanks for any help.

Darron

 

[Frank Kabel]

Hi
one way: use the following user defined function:
Public Function Sum_Color(rng as range,col_index)
Dim ret_value
Dim cell as range
For each cell in rng
If cell.interior.colorindex=col_index then
If IsNumeric(cell.value) then
ret_value = ret_value + cell.value
end if
end if
next
Sum_Col = ret_value
end Function

Call it in your worksheet with:
=SUM_COLOR(B5:B223,3)

 

[Guest]

this worked great thanks

-----Original Message-----
Hi
one way: use the following user defined function:
Public Function Sum_Color(rng as range,col_index)
Dim ret_value
Dim cell as range
For each cell in rng
If cell.interior.colorindex=col_index then
If IsNumeric(cell.value) then
ret_value = ret_value + cell.value
end if
end if
next
Sum_Col = ret_value
end Function

Call it in your worksheet with:
=SUM_COLOR(B5:B223,3)

--
Regards
Frank Kabel
Frankfurt, Germany



.