Formula Closed

Question

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

ac3mark · Answer

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.

TrowaD · Answer

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

Formula

2 responses

Similar discussions

Subscribe To Our Newsletter!