Count based on font colour

[Ali Dharamshi]

I run a sports club and have a number of sport sessions running during the week (see calendar image) at different locations with different prices depending on what session it is but they are regular each week.

The fonts marked in red are cancelled sessions (due to various reasons) but those marked in black are what will be running as normal.

What I'm trying to do is to find a way to reconcile this with monthly invoices received from the venue or atleast work out how much it will cost before recieving the invoice for eg gents football 4x sessions in May X by £40.

So I want it to count number of sessions held in the month or in that tab which are based on 2 criteria:

1. Type of session: eg based on the text
2. Black fonted only (ignoring cancelled as will not be charged for this)

Happy to share the file if it helps

 

[offthelip]

I have a useful function that does a count by color, you can use it just like any other excel function when you have it in your workbook;
To use it has two inputs the first is a single cell which is used to determine the color of the font it counts, the second is the range to do the count over :
here is the code:

Function countByColor(FontColor As Range, rRange As Range)
Application.Volatile
Dim cSum As Double
csum=0
Dim ColIndex As Integer
ColIndex = FontColor.Font.Color
For Each cl In rRange
If cl.Font.Color = ColIndex Then
cSum = cSum + 1
End If
Next cl
countByColor = cSum
End Function

call it like this:

=countbycolor(A2,A2:A24)

In this example the font color in A2 is the color to count. The first range can be anywhere, it doens't need to coincide withthe second range