formula explanation
 

I am trying to adjust a formula to include more cells...I don't understand
the formula that well. It's for a employee work schedule. I've changed it
to go from hourly to every half hour. If there is text within certain cells
then it should equal a half hour of work for that employee. The formula
right now based on hourly is: =SUM(IF(ISTEXT(C6:K7),1,0)) The schedule goes
from 7am to 3pm (example c5 is 7am, then d5 is 8am and so on until 3pm) I
want to change: c5 to 9am, d5 to 9:30, e5 to 10:00 and so on all the way to
7pm. If there is any text within c6 and w6 (which would be 7pm) then I want
the formula to give me total hours worked.

formula explanation
 

Since each now only represents half an hour, we'll change the counter from 1
to 0.5:
=SUMPRODUCT(0.5*ISTEXT(C6:W6))

Note that the previous formula was an array formula, while this one is not.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hoops" wrote:

I am trying to adjust a formula to include more cells...I don't understand
the formula that well. It's for a employee work schedule. I've changed it
to go from hourly to every half hour. If there is text within certain cells
then it should equal a half hour of work for that employee. The formula
right now based on hourly is: =SUM(IF(ISTEXT(C6:K7),1,0)) The schedule goes
from 7am to 3pm (example c5 is 7am, then d5 is 8am and so on until 3pm) I
want to change: c5 to 9am, d5 to 9:30, e5 to 10:00 and so on all the way to
7pm. If there is any text within c6 and w6 (which would be 7pm) then I want
the formula to give me total hours worked.

formula explanation
 

That is an array formula. You will want it to look like this

=SUM(IF(ISTEXT(C6:W6),0.5,0))

As an array formula it must be committed with Ctrl+Shift+Enter and not just
Enter. Once entered XL will automatically add {} around the formula to
indicate that it is an array formula.
--
HTH...

Jim Thomlinson


"Hoops" wrote:

I am trying to adjust a formula to include more cells...I don't understand
the formula that well. It's for a employee work schedule. I've changed it
to go from hourly to every half hour. If there is text within certain cells
then it should equal a half hour of work for that employee. The formula
right now based on hourly is: =SUM(IF(ISTEXT(C6:K7),1,0)) The schedule goes
from 7am to 3pm (example c5 is 7am, then d5 is 8am and so on until 3pm) I
want to change: c5 to 9am, d5 to 9:30, e5 to 10:00 and so on all the way to
7pm. If there is any text within c6 and w6 (which would be 7pm) then I want
the formula to give me total hours worked.

formula explanation
 

This formulas begins by counting how many cells in the range C6:K7 have text
(not numbers) in them. If the answer is more than zero, the formula returns
1 otherwise it returns 0.

I suspect you mistyped that and want only one row:
=SUM(IF(ISTEXT(C6:K6),1,0)) (K6 not K7)

To cover more cells use =SUM(IF(ISTEXT(C6:W6),1,0))
Now here is the critical part. This is an ARRAY formula so you must commit
it with CTRL+SHIFT+ENTER not just ENTER

An alternative formula would be =COUNTA(C6:W6)-COUNT(C6:W6). This is NOT an
array formula. If counts how many cells are not empty and subtracts how many
have number values.

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Hoops" wrote in message
...
I am trying to adjust a formula to include more cells...I don't understand
the formula that well. It's for a employee work schedule. I've changed
it
to go from hourly to every half hour. If there is text within certain
cells
then it should equal a half hour of work for that employee. The formula
right now based on hourly is: =SUM(IF(ISTEXT(C6:K7),1,0)) The schedule
goes
from 7am to 3pm (example c5 is 7am, then d5 is 8am and so on until 3pm) I
want to change: c5 to 9am, d5 to 9:30, e5 to 10:00 and so on all the way
to
7pm. If there is any text within c6 and w6 (which would be 7pm) then I
want
the formula to give me total hours worked.