Huge sum function formula

[John Krsulic]

First I want to say that I just discovered this board and cannot believe the
help I have gotten in the last few days. No more having to pour over books
looking for formulas.
Now I hope I can expalin my prediciment. I have twelve worksheets (one for
each month) that lists employees in column A (A10 - A16). In row 8 and
columns B - AS I have the workdays of the month (Teethless MaMa helped me
with that formula). So therefore one sheet might have more columns accross if
there are 31 days. I also have two columns under each date seperated into
education and consulting. I need to come up with a grand total at the end for
all of the education and all of the consulting hours for each month and each
person. I have been using a sum formula (B10+D10+F10+H10...........AR10) for
the education hours and the sum formula (B11+D11+F11+H11.......AS10) for the
consultative hours. The other wrinkle I need here is the employees need to
type in the letters PTO when they are off. I know that the sum function will
let me do that but I keep getting the wrap around error message.

 

[N Harkawat]

Instead of adding individual cells manually see if this works: -

=SUMPRODUCT(--(MOD(COLUMN(B10:AR10),2)=0),B10:AR10)

This ignores all non-text entries.....Be careful NOT to insert any columns
in this w/sheet in formula range in future lest the formulas will give you
different results!!! I therefore hard code the column # in a separate row 7 &
work the formulas of that row...

 

[T. Valko]

You can make the formula robust against column insertions (as long as those
insertions are done in front of the range).

=SUMPRODUCT(--(MOD(COLUMN(B10:AR10)-COLUMN(B10),2)=0),B10:AR10)

For example, if you insert a new column B the above formula will still work
properly.

 

[driller]

i am wondering for some more clues related with OP's first post...

that lists employees in column A (A10 - A16)
I also have two columns under each date seperated into education and consulting. I need to come up with a grand total at the end for all of the education and all of the consulting hours for each month and each person
I have been using a sum formula (B10+D10+F10+H10...........AR10) for the education hours and
the sum formula (B11+D11+F11+H11.......AS10) for the consultative hours.

?Does each person may consume education and consultative hours?
?Does each person is represented by two adjacent rows of data? One row for
education and next row for consultative?
?You need grandtotal per Row per Person? (one total for Education and One
Total for Consultative)
?You need grandtotal per Sheet? (one total for Education and One Total for
Consultative)

with due respect and assuming, u don't yet received the formula u need,
maybe it may help a lot if you post next here your PARTIAL data on range
(e.g) A8:G8...

regards,
driller