Sum contents of whole column 3

[OMoore]

Hi all,

I need to sum all the data in a column below the fourth row, ie Sum(A5:AInfinity)

Does anyone know what AInfinity should be?

Thanks
Owen

 

[Molby]

65536 is the amount of rows that Excel has.

 

[barbola]

You could highlight the whole range and give it a name such as ColumnA and use that range name in your formulas.

Thanks!
Barb E.

 

[xlbo]

=SUM(INDIRECT("A%:A" & COUNTA(A:A)))

should do the trick nicely

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

I gather that should be A5 and not A% eh?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[OMoore]

Thanks Molby and Barb.
I've set all my ranges to cell 65536. I didn't use the range option as I have some text in the same column as my data.

Thanks a lot
Owen

 

[xlbo]

LOL - that'd be MY phat fingers that time then
touche Mr UK !

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[OMoore]

Geoff

I tried your formula but it is not working for me.

=SUM(INDIRECT("A5:A" & COUNTA(A:A)))

Problem is that my first 4 rows are text which the formula can't add obviously. Tt works if I change the rows to text but for my application I can't do this.

Owen

 

[AnotherHiggins]

Maybe I'm crazy, but why not

[COLOR=blue white]=Sum(A:A)[/color]

Nice and short. Any text in column A will be ignored.

[tt]__________________________________________
My name is John, and I approved this post.[/tt]

To get the best answers fast, please read faq181-2886

 

[DaleWatson123321]

Owen,

Using Excel's DSUM function will definitely work. If you haven't yet used any of these "database" functions, then you'll need to invest an extra minute or two, but you'll be VERY pleased with the results, and your "added knowledge" for your Excel "toolkit".

These database functions require that a field name be at the top of the range being added. If you have more fields that you would want to add, count, average, etc, then you should have a different (unique) field name for each column. And of course these field names need to be on the SAME row.

Note that field names preferably should not have any spaces. This can sometimes mean having to set up a separate row specifically for the field name(s). But you can later hide this row, so it doesn't have to be a visual problem.

Next, assign a range name to the data range. If, for example, your field names are on row 4, and you want to include ALL rows (to 65536), use these steps:

1) Highlight the range - A4:A65536 - if you only want to reference Column A, or A465536 - if you want to include the first 4 columns.
2) Hold down <Ctrl> while you hit <F3>.
3) Type the name you want to assign to the range - in the example formula below, I've used the name "data".
4) Hit <Enter>.

Here's an example of a formula that uses the DSUM function.
=DSUM(data,1,crit)

The formula is made up of 3 parts - separated by the commas.

The first part refers to the range where the data is located. As mentioned, I've used the range name "data".

The second part (1 in this case) refers to the column that you want the function to reference - 1 in this case because Column A is column 1.

The third part refers to a range name "crit". I've arbitrarily called this range "crit". You can assign whatever name you prefer - but again, don't use spaces.

It's preferable to place the criteria on a SEPARATE sheet. This keeps it out of the way, but more importantly, Excel can cause problems if the criteria is placed on the SAME sheet as the data.

In creating the criteria, do as follows:

1) If the field name, for example, is "field1", then enter this formula in a cell: =field1<>"". This will give you "#NAME?" which is quite normal for this situation. (It's in Excel's documentation - just not easy to find.)

2) Next create the range name that you decide to use for naming the criteria range (In my example, I've used the name "crit"). IMPORTANT: Assign this range name to TWO cells - the once containing the formula, and the blank cell immediately above the formula.

You can now enter the formula anywhere you prefer, and it will work.

Note: An alternate way of entering the above criteria, would be to enter the field name ("field1" in this case) in the one cell, and in the cell below, enter: <>""

However, I've purposely recommended the "formula" method of creating the criteria - because there are VAST options that become available once you begin to use formulas in the criteria. You can create compound formulas "as long as your arm" - or as complex as is required to isolate the exact data you want to summarize from your database.

I hope this helps. If you need further help, I can always email you an example file.

Regards,

Dale Watson dalwatson@gov.mb.ca

 

[barbola]

Dale, that sounds so complicated. I probably could use it, but I've never had success with DSUM.

~a fellow 'toban~



Thanks!
Barb E.

 

[OMoore]

Dale,

Thanks very much for the very detailed reply. As Barb said, it does sound very complicated however I can see a use for it. For the moment I think I will go with the "=Sum(A:A) option which is incredibly simple (well done anotherhiggins).

I have sum experience using VBA code in some of my access databases and have used DLookup etc. Can you send me an example of where you've used it?

Thanks,
Owen

 

[DaleWatson123321]

Barb,

From one 'toban to another...

If you "probably could use it, but never had success with DSUM"... perhaps you should allow me to demonstrate what you've been missing.

An ideal "intro" for you to learning how to deal with DSUM and the other database functions... If you still have a file kicking around where you attempted to use DSUM, email it to me and I'll fix it up for you. Naturally if the file contains any sensitive info, replace it with dummy data, but the data should still reflect the type of info you're dealing with.

In addition, if you were to include a "wish list" of the types of summary information you want to generate from your worksheet-based database, it would then allow me to provide you with examples of the "power" of this relatively unused component of Excel.

Pivot tables are always an alternative and probably should be used whenever possible. However, there are some situations where Excel's "Advanced Filter" functions can be "more flexible" in generating special custom reports.

Hope this helps.

Regards,

Dale Watson dalwatson@gov.mb.ca

 

[DeLaMartre]

You are brilliant as ever, Dale; Thanks for the reminder about database functions!

Sincerely,

-Bob in California

 

[barbola]

I'll take a look Dale - I use pivot tables alot but if I have any questions I'll email you.

thanks!

Thanks!
Barb E.