自定义函数实现
按Alt+F11进入VBA,右击左边“工程-->插入-->模块”(或单击菜单“插入-->模块”),在模块1代码窗口输入:
计数公式:
Function CountColor(col As Range, countrange As Range)
Dim i As Range
Application.Volatile
For Each i In countrange
If i.Font.ColorIndex = col.Font.ColorIndex Then
CountColor = CountColor + 1
End If
Next
End Function
如数据区域为A1:B10,在统计单元格中输入公式=CountColor(A2,A1:B10) 按F9,其中A2为想统计颜色的字体所在的单元格
求和公式:
Function Sumcolor(col As Range, countrange As Range)
Dim i As Range
Application.Volatile
For Each i In countrange
If i.Font.ColorIndex = col.Font.ColorIndex Then
Sumcolor =Application.Sum(i) +Sumcolor
End If
Next
End Function
如数据区域为A1:B10,在求和单元格中输入公式=Sumcolor(A2,A1:B10) 按F9,其中A2为想统计颜色的字体所在的单元格
如果想统计不同背景色的数字,将Font改为Interior