I want to write a formula that sums up the entries in column A from A5 and down. Problem is I don’t know how many rows of data might be entered, so I don’t want to specify the last cell in the range. I DO need to specify the first cell, as the first few rows have some text and a column header.
Cheap-and-nasty solution: if you can estimate the total number of likely entries, make it =sum(B5:B500) or whatever and don’t put anything else in the column. I’m sure there’s a genuinely elegant way to do it, but that’s what I’d do.
My way of doing it would be similar to jjimm’s, but with 65536 as the top entry. As in:
=SUM(A5:A65536)
Excel only allows you to have 65536 rows in a worksheet, so if you did manage to overshoot that, you’d have bigger problems than the summation missing some entries.
You can also skip rows in a column too, with the sum function:
Just sum the whole column: e.g. =SUM(A:A). The non-numerics in A1 through A4 won’t enter the calc at all and you’ll be fine.
As the others said, the second choice is to use =SUM(A5:A65536). You say you “don’t want” to do that. Why not? The only potential harm in doing so is if you may eventually have unrelated data further below that ought not be in the sum.
The purest, safest, choice is to put a guard row below your last row of data. Something that says “Enter all data ABOVE this row …” Let’s say you start with 5 rows of data in A5:A9 and put your guard row in A10. Then make your formula =SUM(A5:A10). Whenever you need to add data, you Insert a row above A10 and the range in the fomula automatically stretches to accomodate the new row.
In fact, the pros usually include the upper header as well, so it’d be =SUM(A4:A10). That way when anyone inserts a row above the top row of figures, that’ll be included in the sum as well.
Late versions of Excel have gotten smarter about stretching range references when rows/columns are added at the ends of ranges, but using guard rows/columns at either end is 100% goof proof.
Well, unless the goof starts inputting data underneath the “do not enter data underneath here” box and then phones you complaining that it doesnt work. :smack:
You can use a solution similar to the first one suggested by LSLGuy, even if A1 through A4 do contain numerics for some reason:
=SUM(A:A) - SUM(A1:A4)
That way, any numerics in A1:A4 that are erroneously added to the sum will be subtracted from the sum afterwards.
I didn’t want to do that only because I didn’t know there was a limit of 65K rows. Now that I DO know, that’s a perfectly acceptable solution.
In all probability there would not be more than a hundred or so rows of data, but I’m trying to “idiot proof” the sheet for people to use when I’m not around. I don’t claim any great expertise in Excel, but I am leaps and bounds ahead of anyone else I work with. Thanks to you folks, I now have the tools to design the sheet in a way that no one need do anything except enter the raw data row by row.
Each row of data is the info from a single individual’s health screening result. We collect systolic and diastolic blood pressure; blood glucose level; total, HDL and LDL cholesterol levels, and triglyceride level. We categorize each of these results into 3 categories --rougly “normal”, “speak to your doctor”, and “speak to your doctor NOW!”.
We do this for employers and their workforces. We give the people back their individual results, but the employer only gets a summary of the data, graphs of what percentage of their employees have higher risks of certain kinds. That’s what this worksheet is about, summing the results and populating graphs with the sums.
My last unsolved problem is about keeping the file size down. Each row of data needs about a dozen fairly long formulas to sort them into the categories, and I don’t wan’t to copy the formulas down 65k rows when 50-200 rows is the norm for data entry. Yes I know it’s quite easy to simply copy that range of equations down as many rows as are needed and no more, but by and large I’m dealing with people who don’t even know how to copy and past in Word, let alone Excel.
I like the “guard row” idea, a visible barrier to data entry beyond a certain point. Either that or some kind of macro or Visual Basic procedure which adds a fresh row of calculations for each row of raw data.
The following macro will copy down formulas for as many rows as you have data. (Actually some extra rows if rows above data, 1-3, are not blank). Assumes first row of data is 4 and formulas run from Column C across to Column H
Sub Macro1()
Range(“c4:h4”).AutoFill _
Destination:=Range(“c4:c4”).Resize(ActiveSheet.UsedRange.Rows.Count, 6)
End Sub