Count but with conditions

[v!v]

I have a table for each person that's periodically updated with essentially 3
columns: A = week ending dates, B= work hours, and C= travel hours. For any
given week, there may be hours logged under columns B and C, OR B or C, OR
neither. I need to calculate the average weekly hours for each person. I
think I need some sort of formula that combines COUNTIF and Blanks but I
can't seem to figure it out. I don't want to count a week twice when they
log both types of hours, and since they could log either type of hours and
not always one or the other, I think this is my problem spot. Hope someone
can help.

 

[v!v]

Work hrs Travel hrs
7/31/08 42
8/2/08 25 18
8/9/08 32
8/16/08
8/23/08 15 15
8/30/08 18
Subtotal 114.00 51.00
# wks with hrs 4.00 3.00
Average weekly hrs 28.50 17.00
Total hrs (all) 165.00
# wks with hrs Formula? for this cell The answer should be 5 but how do I
get it?
Average overall weekly hrs then becomes a simple division with the total hrs
divided by what the # of wks with hours turns out to be. I'm guessing this
is one of the formulas that nested?

 

[v!v]

Thanks Smartin! I like the one step formula. - that will the simplest to
incorporate. But let me make sure I understand what the steps are & why they
are done: 1) Since we're dealing with a couple of columns of data that our
formula has to involve, we're using an array function as indicated by the
special keystroke (ctrl/shift/enter) at the end. 2) If either col B or col C
on the same row have a value in them, the sum (indicated by the +) would be
greater than 0, therefore the (B2:B7+C2:C7 >0) since the addition of the two
cells is done first and then compared to 0. 3) The next couple parts are
unfamiliar to me (I've never done arrays before if that's a good excuse.) -
from your alternative solution, do the two minus signs in front of part 2
(adding the rows & comparing to 0) automatically mean do this for each row
within the data set? And then for part 4) I don't get that if you're
calculating a COUNT, why are you saying SUM at the start of the formula? I
know that it works but I don't "get" the last couple steps.
Thanks

 

[v!v]

Ohhhhhhhh! I get it! Thanks sooo much. Your explanation made all the
difference - I had read a lot of the other posts on COUNTs but the "higher"
math threw me - (I don't think they had that level when I was in school.) I
can dazzle them at work once again. Glad I found this site as it's hard to
look up something that's alittle beyond the basic Excel intermediate level if
you don't know what to call it. I'll check out the website at work (dealing
with dialup here at home and maybe by then, it will be available again.)
Thanks again - guess I'm done here.