Sum of Range of Cells
 

I have an excel sheet which we track manhours in our place of work all on one
row. I wish to have a sum of those hours displayed in a cell.
The part causing me problems is.
Any value greater than 10=10
Any letter value of "H", "W", or "SCH" also =10
Anything else = 0

Is it possible to simply gather the sum of a range of cells, and apply these
conditions?
I've done this so far, but then I have to apply this formula for every cell
off the page and then tally the results.
=IF(ISNUMBER(F37),(IF(F3710,10,F37)),(IF(OR(F37=" H",F37="W"),10,0)))

I do this for every cell, then do a sum of all these cells to get my result.

I also run into problems when the user adds a new row, the formula is lost.
It's easy to just fill down the formula, but would be nice to have it remain
even when a new row is added.

Thanks, so far, this has been an interesting learning experience.

Hi!

How about posting something like 10 to 15 cells worth of typical data and
the result you expect from that data.

Biff

"Jonathon" wrote in message
...
I have an excel sheet which we track manhours in our place of work all on
one
row. I wish to have a sum of those hours displayed in a cell.
The part causing me problems is.
Any value greater than 10=10
Any letter value of "H", "W", or "SCH" also =10
Anything else = 0

Is it possible to simply gather the sum of a range of cells, and apply
these
conditions?
I've done this so far, but then I have to apply this formula for every
cell
off the page and then tally the results.
=IF(ISNUMBER(F37),(IF(F3710,10,F37)),(IF(OR(F37=" H",F37="W"),10,0)))

I do this for every cell, then do a sum of all these cells to get my
result.

I also run into problems when the user adds a new row, the formula is
lost.
It's easy to just fill down the formula, but would be nice to have it
remain
even when a new row is added.

Thanks, so far, this has been an interesting learning experience.

Try...

=SUM(IF(ISNUMBER(F37:L37),IF(F37:L3710,10,F37:L37 ),IF(ISNUMBER(MATCH(F37
:L37,{"H","W","SCH"},0)),10)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range
accordingly.

Hope this helps!

In article ,
"Jonathon" wrote:

I have an excel sheet which we track manhours in our place of work all on one
row. I wish to have a sum of those hours displayed in a cell.
The part causing me problems is.
Any value greater than 10=10
Any letter value of "H", "W", or "SCH" also =10
Anything else = 0

Is it possible to simply gather the sum of a range of cells, and apply these
conditions?
I've done this so far, but then I have to apply this formula for every cell
off the page and then tally the results.
=IF(ISNUMBER(F37),(IF(F3710,10,F37)),(IF(OR(F37=" H",F37="W"),10,0)))

I do this for every cell, then do a sum of all these cells to get my result.

I also run into problems when the user adds a new row, the formula is lost.
It's easy to just fill down the formula, but would be nice to have it remain
even when a new row is added.

Thanks, so far, this has been an interesting learning experience.

Awesome!
I've tried something similar but it didn't work. I also didn't go control
shift enter, for an array. I appreciate the help, I'll check out what you
just told me so I completely understand what I'm doing, but it is working,
thanks.

"Domenic" wrote:

Try...

=SUM(IF(ISNUMBER(F37:L37),IF(F37:L3710,10,F37:L37 ),IF(ISNUMBER(MATCH(F37
:L37,{"H","W","SCH"},0)),10)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range
accordingly.

Hope this helps!

In article ,
"Jonathon" wrote:

I have an excel sheet which we track manhours in our place of work all on one
row. I wish to have a sum of those hours displayed in a cell.
The part causing me problems is.
Any value greater than 10=10
Any letter value of "H", "W", or "SCH" also =10
Anything else = 0

Is it possible to simply gather the sum of a range of cells, and apply these
conditions?
I've done this so far, but then I have to apply this formula for every cell
off the page and then tally the results.
=IF(ISNUMBER(F37),(IF(F3710,10,F37)),(IF(OR(F37=" H",F37="W"),10,0)))

I do this for every cell, then do a sum of all these cells to get my result.

I also run into problems when the user adds a new row, the formula is lost.
It's easy to just fill down the formula, but would be nice to have it remain
even when a new row is added.

Thanks, so far, this has been an interesting learning experience.