Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple SUMIF Question
I am an ASP.NET programmer trying to use a 3rd party product to produce Excel
spreadsheets. I know very little about Excel, so my questions may seem simplistic to most of you. I have a sheet where every other row alternates between numeric and text values. Actually the odd numbered cells such as E:5, E:7 etc. contain the numbers. The even numbered rows I have merged across the entire width of the data area to display long text that is wrapped. I have a footer row in which I want to display the SUM of the odd numbered cells in column E, or the cells that contain numeric data which can be summed. The problem is, I never know in advance how may rows will be produced as it depends on what data the Website user wants to display. I think I can use the SUMIF function but I have no idea what to put in the Criteria. Something like ISNUMERIC(Cell.Value)? BTW -- Where can I find a good listing of all the Excel functions available to use? Thanks for any responses. -- "Building a better mouse trap doesn''''t necessarily make it better for the mouse." |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple SUMIF Question
Actually, if you have text in those cells with even number rows, you can use a
simple =sum() to add up the values in a column. =Sum() will ignore all those text values. I don't quite understand where you're putting this formula, but you could use: =sum(e:e) in any cell not in column E to add up all those values in column E. You could look at Excel's help or... Take a look at Peter Nonely's workbook that describes lots of functions: http://homepage.ntlworld.com/noneley/ Norman Harker has his version at Debra Dalgleish's site: http://www.contextures.com/functions.html John Kotuby wrote: I am an ASP.NET programmer trying to use a 3rd party product to produce Excel spreadsheets. I know very little about Excel, so my questions may seem simplistic to most of you. I have a sheet where every other row alternates between numeric and text values. Actually the odd numbered cells such as E:5, E:7 etc. contain the numbers. The even numbered rows I have merged across the entire width of the data area to display long text that is wrapped. I have a footer row in which I want to display the SUM of the odd numbered cells in column E, or the cells that contain numeric data which can be summed. The problem is, I never know in advance how may rows will be produced as it depends on what data the Website user wants to display. I think I can use the SUMIF function but I have no idea what to put in the Criteria. Something like ISNUMERIC(Cell.Value)? BTW -- Where can I find a good listing of all the Excel functions available to use? Thanks for any responses. -- "Building a better mouse trap doesn''''t necessarily make it better for the mouse." -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple SUMIF Question
Thanks Dave,
As you can tell I am a real Excel novice. I had no idea that =Sum() would simply ignore the text values. I guess I was putting too much thought into it. SQL Server would not have been as forgiving. In fact I would never have thought that I could just write =Sum(e:e) to get a correct result. I was trying to calculate the index of the last row of data (as the worksheet is being built dynamically in VB code) and therfore trying to determine the last data row so I could parse a formula such as =SUM(E3:E47) where the 47 would be a result of keeping track of the row indexes as the rows are written by the program. I am placing the formula in a "footer" row at the bottom of the last row of data in the same column position (e) as the data I am summing. Thanks again... -- "Building a better mouse trap doesn''''t necessarily make it better for the mouse." "Dave Peterson" wrote: Actually, if you have text in those cells with even number rows, you can use a simple =sum() to add up the values in a column. =Sum() will ignore all those text values. I don't quite understand where you're putting this formula, but you could use: =sum(e:e) in any cell not in column E to add up all those values in column E. You could look at Excel's help or... Take a look at Peter Nonely's workbook that describes lots of functions: http://homepage.ntlworld.com/noneley/ Norman Harker has his version at Debra Dalgleish's site: http://www.contextures.com/functions.html John Kotuby wrote: I am an ASP.NET programmer trying to use a 3rd party product to produce Excel spreadsheets. I know very little about Excel, so my questions may seem simplistic to most of you. I have a sheet where every other row alternates between numeric and text values. Actually the odd numbered cells such as E:5, E:7 etc. contain the numbers. The even numbered rows I have merged across the entire width of the data area to display long text that is wrapped. I have a footer row in which I want to display the SUM of the odd numbered cells in column E, or the cells that contain numeric data which can be summed. The problem is, I never know in advance how may rows will be produced as it depends on what data the Website user wants to display. I think I can use the SUMIF function but I have no idea what to put in the Criteria. Something like ISNUMERIC(Cell.Value)? BTW -- Where can I find a good listing of all the Excel functions available to use? Thanks for any responses. -- "Building a better mouse trap doesn''''t necessarily make it better for the mouse." -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple SUMIF Question
Using Excel's VBA, I'd use something like:
Dim LastRow as long with worksheets("somesheetnamehere") 'based on the data in column A lastrow = .cells(.rows.count,"A").end(xlup).row .cells(lastrow + 1, "E").formular1c1 _ = "=sum(r3c:r[-1]c)" end with By using the R1C1 reference style in my formula (formular1c1), it makes it a little easier. r3c means row 3, same column as the formula cell r[-1]c means one row up from the formula cell and the same column. John Kotuby wrote: Thanks Dave, As you can tell I am a real Excel novice. I had no idea that =Sum() would simply ignore the text values. I guess I was putting too much thought into it. SQL Server would not have been as forgiving. In fact I would never have thought that I could just write =Sum(e:e) to get a correct result. I was trying to calculate the index of the last row of data (as the worksheet is being built dynamically in VB code) and therfore trying to determine the last data row so I could parse a formula such as =SUM(E3:E47) where the 47 would be a result of keeping track of the row indexes as the rows are written by the program. I am placing the formula in a "footer" row at the bottom of the last row of data in the same column position (e) as the data I am summing. Thanks again... -- "Building a better mouse trap doesn''''t necessarily make it better for the mouse." "Dave Peterson" wrote: Actually, if you have text in those cells with even number rows, you can use a simple =sum() to add up the values in a column. =Sum() will ignore all those text values. I don't quite understand where you're putting this formula, but you could use: =sum(e:e) in any cell not in column E to add up all those values in column E. You could look at Excel's help or... Take a look at Peter Nonely's workbook that describes lots of functions: http://homepage.ntlworld.com/noneley/ Norman Harker has his version at Debra Dalgleish's site: http://www.contextures.com/functions.html John Kotuby wrote: I am an ASP.NET programmer trying to use a 3rd party product to produce Excel spreadsheets. I know very little about Excel, so my questions may seem simplistic to most of you. I have a sheet where every other row alternates between numeric and text values. Actually the odd numbered cells such as E:5, E:7 etc. contain the numbers. The even numbered rows I have merged across the entire width of the data area to display long text that is wrapped. I have a footer row in which I want to display the SUM of the odd numbered cells in column E, or the cells that contain numeric data which can be summed. The problem is, I never know in advance how may rows will be produced as it depends on what data the Website user wants to display. I think I can use the SUMIF function but I have no idea what to put in the Criteria. Something like ISNUMERIC(Cell.Value)? BTW -- Where can I find a good listing of all the Excel functions available to use? Thanks for any responses. -- "Building a better mouse trap doesn''''t necessarily make it better for the mouse." -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple SumIF Question? | Excel Worksheet Functions | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple SUMIF, I think... | Excel Worksheet Functions | |||
this may be simple, sumif question | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) |