Summing multiple rows in Excel 1

[sgreenwood]

Is there an easy way to sum multiple non-contiguous rows in Excel? For example +a1+a10+a20+a30+a40. Other than pressing +a1+a10+a20+a30 etc?

Any help with this would be greatly appreciated!

 

[Dreamboat]

What is in between those cells?
Why is your data set up this way?
If you'd like, I'll review your Excel worksheet so it's set up to work WITH Excel and FOR you.

Basic *rules*:

First row contains column headings.
Second row contains first record.
Third row contains second record, and so on.

No completely blank rows and no completely blank columns within your data range.

If you've got sums and blank rows and more sums, you're not doing it the easiest way.

http://www.TheWordExpert.com

 

[sgreenwood]

Which is exacly how *I* would set up a spreadsheet. This is one that I have "inheirited" and is set up as follows:


1999 2000 2001 2002 2003
TABLE GRAPES
Hectares planted 50
Kilograms/Hectare 25,000
Total Kilograms 1,250,000
$ per Kilogram 2
Total Dollars 2,500,000
Royalty Rate 3%
Est. Royalties 75,000

PLUMS
Hectares planted 50
Kilograms/Hectare 25,000
Total Kilograms 1,250,000
$ per Kilogram 2
Total Dollars 2,500,000
Royalty Rate 3%
Est. Royalties 75,000

The data continues on to the right up through 2020 and continues down for as many different crops that are going to be planted.

What I'm attempting to do is find an easy way to sum the total hectares planted, total kilograms and total dollars at the bottom of the report.

I can think of several different programs to do this in that would probably be better and easier than excel, but I don't have the option to change it.

 

[svanels]

I would put the fruits on different sheets, and do all the processing in a special sheet S. van Els
SAvanEls@cq-link.sr

 

[euskadi]

sgreenwood,

What we're all (more or less) trying to get at, is we're wondering what in particular is unique about those rows you've indicated. If there is something, such as a column of names (maybe a1, a10, a20 etc is "sub-total" then it's easy to sum by those rows without clicking on the individual cells.

 

[sgreenwood]

Thanks for your response euskadi...in answer to your question, I believe that's basically my problem....there really isn't anything unique. If the data was a subtotal etc. I would be able to easily accomplish what I'm after. As I mentioned earlier, I have inheirited this spreadsheet, and it's a very large, very involved spreadsheet that unfortunately I don't have the option of "re-inventing". There are about 40 worksheets arranged as I showed above (one worksheet for each country). It's just a very tedious process to try to sum the individual cells. I guess what I'm hoping to accomplish is to find some method whereby I can "add every 8th cell" in the column.

I'm sure there is some way to do this using VBA...I guess I was just hoping for the proverbial "easy way out"!!

Again, thanks very much for your responses.

 

[Guest_imported]

There are a few ways to go about this.
But I need to know are you summing ROWS or CELLS?
for example SUM(22:22,25:25,40:40) Will summ the rows.
Now there is a formula you can make that will litterally sum based on a number. You have to use a few built in formula functions. INDIRECT(ADDRESS())
With in address use ROW()-x
you use ADDRESS to build your cell refrence. You use ROW() to return what row you are currently on. If you want to sum a cell 3 rows back you could say
=INDIRECT(ADDRESS(ROW()-3,COLUMN()))
Where ADDRESS builds a text Cell Refrence from numbers, and INDIRECT converts it to a real refrence. Quite often I use this with MATCH() to locate an Item and build a refrence to it.

Master$Merlin Cul Kirkpatrick
Merlin@VBScripts.com
Merlin@Dangerous-Minds.NET

Good luck to you.

 

[Dreamboat]

Can we do a SUMIF hectares....? I had problems with it when there were blank cells in between, so I know there's a special way of doing it. I may have it at work. Lemme look.

Meanwhile, you can check out:

http://www.cpearson.com/excel/array.htm

http://www.TheWordExpert.com

 

[DaleWatson123321]

If you'd care to send me a "scaled down" version of the file, I'd be happy to set up a matrix summary using database formulas.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[sgreenwood]

Thank's everyone very much for your responses! Let me take a moment to digest your suggestions and I will be back with you.

This forum truly amazes me! It's wonderful to find people so willing to lend a hand!

 

[euskadi]

Incidentally, if you really want to sum every eighth row, this will do it:

=SUM(IF(MOD(ROW(A1:A28)-(Base-1),Steps)=0,A1:A28,0))

Array forumla... do CTRL+SHFT+ENTER instead of ENTER.

Where Base is the first row you want to sum, and Steps is the number of rows you're counting by (if steps = 3 you'll get every third row).

 

[sgreenwood]

Thanks EVERYONE for you help and suggestions.

What actually worked for me was using the "DSUM" database function with the criteria "hectares". Worked like a charm.

Thanks Dale for pointing me to the database functions!


All of your help is appreciated very much.

 

[Dreamboat]

Ahhh....another happy camper in tek-tipville.

http://www.TheWordExpert.com

 

[DaleWatson123321]

You're QUITE welcome !!!

I'm pleased you were able to resolve the task yourself.

Congratulations, and thanks for the STAR.

Regards, ...Dale