Formula

Closed
Jenna3009 Posts 1 Registration date Thursday June 6, 2013 Status Member Last seen June 6, 2013 - Jun 6, 2013 at 04:22 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jun 6, 2013 at 10:36 AM
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

Blocked Profile
Jun 6, 2013 at 10:07 AM
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.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jun 6, 2013 at 10:36 AM
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
0