How to count number of red and green cells

[yaseen3737]

Hi,

I am making my work schedule sheet for evey month.

Like if i am working from 1st to 15, then i mark those cells as red, and if i am off from 16 to 30th, then i mark them green. Now what formula should i use to calculate number of red cells and number of green cells. And i want to display the total count in a cell named Nf days work, No.of days off.

 

[NARAYANK991]

Hi ,


Are you comfortable with Excel VBA ? If so , the following link has all that you want :


http://www.cpearson.com/excel/colors.aspx


Narayan

 

[yaseen3737]

Thanx Narayan,

But i am not too good in VB. I remeber one of my friend use to do it with some formula in excell. Please let me know if you have easy solution for it.

 

[Hui]

Yaseen3737


Firstly welcome to the Chandoo.org Forums


Have a look at this User Defined Function (UDF)


It can either Count or Sum the cells that match a reference cell


To use copy the code below into a VBA Code Module


In use

=Cell_Math(Range, Ref Cell, Type)

Range is your Range

Ref Cell is a Reference cell, it can be part of the range if required

Type = 0 Sum cell values that match Ref Range color

Type = 1 Counts cells that match Ref Range color (Default)


eg:

Count the cells in A1:A100 that have the same background color as B1

=Cell_Math(A1:A100, B1, 1)

or

=Cell_Math(A1:A100, B1)


Sum the cells in A1:A100 that have the same background color as B1

=Cell_Math(A1:A100, B1, 0)

[pre]

Function Cell_Math(myRange As Range, source As Range, Optional myType As Integer = 1) As Integer
'
' Use =Cell_Math( Range, Ref Range, Type)
'
' Type=0 Sum cell values that match Ref Range color
' Type=1 Counts cells that match Ref Range color

Cell_Math = 0
For Each c In myRange
If c.Interior.Color = source.Interior.Color Then
If myType = 1 Then
Cell_Math = Cell_Math + 1
Else
Cell_Math = Cell_Math + c.Value
End If
End If
Next
End Function

[/pre]

 

[Hui]

To install copy the code

in your worksheet Alt F11

On the left find your worksheet and Right Click

Insert Code Module

In the right hand pane, Paste

Go back to your worksheet, Alt F11


=Cell_Math(...)

 

[yaseen3737]

Hui,


Thanks a lot for your explenations.

I am too dumb to understand all this.

Is it possible that i send my excell sheet and you do this job for me and send it back? then i can see how have you done it and then try my luck?

Let me know how can i send the sheet to you.

 

[Hui]

Received and responded

 

[unnimp]

As an alternative to the code, you could sort the cells by colour, and count them.

Cheers

 

[Ri0]

You could just input a number or letter into the cells that you are working and use conditional formating to make the cells red or green. Make the number or letter the same color and you will only see the color. That way you can use the =Count() formula. Hope this helps.