list help

[ionstorm20]

Hopefully some of you will be able to help me out on this one.

I don't have any real knowledge with VB so that's not really an option, but I'm trying to build a list of statistics, and need to know what I have to do to have someone enter a number of columns that they can use, and all up all the numbers in the column

Ex
B1:2
B2:4
B3:6
B4:8
B5:10
B6:12


In square h4 they type in 3 and it will give them an answer of 12...(2+4+6)

 

[Rick Rothstein]

Give this formula a try...

=SUM(INDIRECT("B1:B"&H4))

 

[ionstorm20]

All that gives me is a result of 0


if it helps, I just put in a list of numbers in the a column that are the base numbers so...

a1:1 B1:2
a2:2 B2:4
a3:3 B3:6
a4:4 B4:8
a5:5 B5:10
a6:6 B6:12

 

[Rick Rothstein]

What cell did you put the formula I posted in? Assuming it was not H4 (because that wouldn't work then), did you in fact type a number into H4?

 

[ionstorm20]

The number you type into is cell h4, the formula is at the bottom of each column.

These results will, in turn, be copied to another list on another sheet

 

[Rick Rothstein]

Then I am not sure why the formula I posted does not work for you... it works on my system when I put the values you indicated into the cells you indicated. I would point out, though, that if you plan to copy the formula across columns, then you cannot use the formula I posted (the part in quote marks won't change). Try this formula instead...

=SUMPRODUCT((ROW(B1:B7)<=$H$4)*B1:B7)

 

[ionstorm20]

Well at least that second formula is giving me a result of 0...it's not the correct answer but it's on the right path

 

[Rick Rothstein]

Can you post your workbook on one of the free file posting sites so that we can see what is going on directly? You can post it online using one of these free posting websites...

MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
Box: http://www.box.net/files

Then post the URL they give you for the file back here.

 

[ionstorm20]

http://www.filefactory.com/file/cbd441c/n/Shin_Experimental_Sheet.xls

The tab is hth charts

 

[Rick Rothstein]

Okay, the main problem is you lied to us. Your original post said your data started in B1, but actually it starts in B9. Put either of these formulas in B24 and see if you get the results you are after...

=SUM(INDIRECT("B9:B"&H4+8))

=SUMPRODUCT((ROW(B9:B23)<=$H$4+8)*B9:B23)

For future questions you may ask... it is always a bad idea to simplify your layout for us.