[excel] Here’s how to count number of same colored cells

Being a Project Manager sometimes you have to do things that you didn’t know existed. There are somethings which are possible in a way, but sometimes they need to be done differently.

I have used excel on-off since 2008. Not like a power-user but working on stuff which should actually use only Excel.

Today came a problem to me (A problem for me, an obvious something to have for the Manager). In a table containing different teams, I had to count the number of Engineers working in Development and Validation for each team.

Not a mean task if the list is small. Not mean when they are set in different sections. Not so mean even if you can use a table’s filter. But it is a mean task, when they’re in the same column and only differentiated by the color of the cell!

Different colors for depicting team-division is good idea. Dynamic counting based on color was not easy to do. Dynamic was anyways important because a team can always increase or decrease in size. It always does, doesn’t it?

On my search for a solution, I found out, which I had actually feared, that there’s no way you could use an excel formula to find color of the cell. EXCEPT…. using a VBA script. So I went for search of a script to let me count the number of cell in a range with a certain cell color.

I found a few, which invariably linked to either 1 of the 2 links on the Internet. I tried them or whatever different variant I could find, but it just didn’t work. That is UNTIL I landed on a different kind of solution to it (doesn’t work) (too complicated)

differentially colored cells

What worked is:

Public Function cell_colour(Cell_Check As Range) As Long
cell_colour = Cell_Check.Interior.Color
End Function

Public Function count_colour(Count_Range As Range, Colour As Long) As Long
Dim x As Range
count_colour = 0
For Each x In Count_Range
If x.Interior.Color = Colour Then
count_colour = count_colour + 1
End If
Next x
End Function

from forum on edugeek.

In the excel cell where you want to get the count, use

=COUNT_COLOUR(RANGE, CELL_COLOUR(CELL))

Best part is, it is dynamic!

There’s a new Developer in Team B. Go to the colored cell of Lead Developer of Team B, click on Format painter, and then click on new member’s name.

Voila! The new member’s cell changes color and you get the updated numbers.

Advertisements

One thought on “[excel] Here’s how to count number of same colored cells

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s