Simple SUMIF Question

[John Kotuby]

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.

 

[Dave Peterson]

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]

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."

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

 

[Dave Peterson]

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.

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...