Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting empty cells within a range of cells | New Users to Excel | |||
automatically fill in a range of cells | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Excel - formula to calculate colored fill cells within a range wi. | Excel Worksheet Functions |