Sum of Range of Cells

[Guest]

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(F37>10,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.

 

[Biff]

Hi!

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

Biff

 

[Domenic]

Try...

=SUM(IF(ISNUMBER(F37:L37),IF(F37:L37>10,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!

 

[Guest]

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.