Formula [Closed]

Report
Posts
1
Registration date
Thursday June 6, 2013
Status
Member
Last seen
June 6, 2013
-
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
-
Hi,

I need to create a formula for a whole team. It is based on the number of people in the office for each calendar month. The spreadsheet I have has the days along the top and the team names are down on the left. Each weekend is highlighted in a certain colour, however I need to create a formula showing how many people are in the office each day (cells, which have remaind white.) can you please assist me in creating such a formula.

Many thanks
jenna

2 replies


Do you need a Macro, or a Formula?

Is each day totaled on its own by workers? So Wednesday would have "5"?
YOu may need to make a hidden column beside the day. Check in the hidden cell what the fromatting is of the cell next to it {if/then statement structure =if(logical condition,true, false)}. If the formatting fits the day has been occpied, then make the hidden cell a 1. At the bottom of that hidden column run the formula =Sum(Z10:Z30) (use whatever column calculations, this exampe uses Column Z as the hidden column to total ).

I hope this helps.
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
461
Hi Jenna,

We need a custom formula for that since you can't retrieve color index using the standard formula's.

Put this public function in a module:
Public Function CountWhiteCells(TargetCells As Range) As Variant

CountWhiteCells = 0

For Each cell In TargetCells
    If cell.Interior.colorIndex = xlNone Then
        CountWhiteCells = CountWhiteCells + 1
    End If
Next cell

End Function

Then use the formula as in:
=CountWhiteCells(A1:B10) or whatever range.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!