Is this possible?? TIA!

[office]

How would the following sentence be written as a valid formula??

Sum cells C4 down to C31 only if ALL of them have a value in them.

also

I want to find the average hours people have worked.
So if I have a total of 20 people working for me & 3 of them were on holiday
last week I want to divide the total hours worked by 17, not 20.
A 0.00 next to there name would mean they haven't worked at all during that
week!
400 hours worked last week by 17 people would mean the averaged 23.52 hours
each

Is there a way I can tell excel to add all the hours worked and then divide
this figure by the amount of times there was a number larger than 0.00 in
the cells it has just summed??

Thanx for any help...
I really am stuck with this one!!

Rick

 

[J.E. McGimpsey]

One way:

=IF(COUNT(C4:C31)=28, SUM(C4:C31),"")

 

[Alan]

=SUM(C4:C31)/COUNTIF(C4:C31,"<>"&0)

Divides the sum of C4:C31 by the number of cells that are not zero in C4:C31

To avoid the dreaded #DIV/0! error if all the cells in C4:C31 are zero, (so
that you are dividing by zero)

=IF(COUNTIF(C4:C31,">"&0)>0,SUM(C4:C31)/COUNTIF(C4:C31,"<>"&0),"")

Alan.