Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 27th 08, 01:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 47
Default Formula to sum non-adjacent cells

Excell 2003
I have a 3000+ line spreadsheet with totals in one column every 17th row.
What formula can I use to sum these totals?

Thanks
Jack

  #2   Report Post  
Old March 27th 08, 01:09 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 141
Default Formula to sum non-adjacent cells

On Mar 27, 9:04*am, Jack_Feeman
wrote:
Excell 2003
I have a 3000+ line spreadsheet with totals in one column every 17th row.
What formula can I use to sum these totals?

Thanks
Jack


If the rest of the rows in that column are empty, you can just sum

Else, a =SUMIF( would work well if there was some way you could
differentiate those rows with a sum ... for example, if the word "sum"
was in a previous column you could use something like this:
=SUMIF(D1:E15,"sum",E1:E15)

HTH

Chris
  #3   Report Post  
Old March 27th 08, 01:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 47
Default Formula to sum non-adjacent cells

Thanks for the really quick reply Chris.
I will try your suggestion. I may have badly worded my question, here is a
better description:
"I have a 3000+ row spreadsheet with totals in the same column every 17th
row."
Would this clarification change your suggestion?

Thanks Jack

"cht13er" wrote:

On Mar 27, 9:04 am, Jack_Feeman
wrote:
Excell 2003
I have a 3000+ line spreadsheet with totals in one column every 17th row.
What formula can I use to sum these totals?

Thanks
Jack


If the rest of the rows in that column are empty, you can just sum

Else, a =SUMIF( would work well if there was some way you could
differentiate those rows with a sum ... for example, if the word "sum"
was in a previous column you could use something like this:
=SUMIF(D1:E15,"sum",E1:E15)

HTH

Chris

  #4   Report Post  
Old March 27th 08, 01:49 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 141
Default Formula to sum non-adjacent cells

On Mar 27, 9:26*am, Jack_Feeman
wrote:
Thanks for the really quick reply Chris.
I will try your suggestion. I may have badly worded my question, here is a
better description:
"I have a 3000+ row spreadsheet with totals in the same column every 17th
row."
Would this clarification change your suggestion?

Thanks Jack



"cht13er" wrote:
On Mar 27, 9:04 am, Jack_Feeman
wrote:
Excell 2003
I have a 3000+ line spreadsheet with totals in one column every 17th row.
What formula can I use to sum these totals?


Thanks
Jack


If the rest of the rows in that column are empty, you can just sum


Else, a =SUMIF( would work well if there was some way you could
differentiate those rows with a sum ... for example, if the word "sum"
was in a previous column you could use something like this:
=SUMIF(D1:E15,"sum",E1:E15)


HTH


Chris- Hide quoted text -


- Show quoted text -


So it's like
1
1
1
1
1
1
1
1
1
1
1
16
1
1
1
1
1
1
1
1
1
1
1
1
1
16

?

If so, the sum of the entire column is twice that of just the "every
17 rows" ...

Chris
  #5   Report Post  
Old March 27th 08, 02:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 100
Default Formula to sum non-adjacent cells

Jack-

You may find that you get more responses and ideas in
microsoft.public.excel.worksheet.functions, which has more of a focus on
worksheet formulas/functions. That said...

I think Chris was indicating that we still don't have enough information to
ensure the best solution. Does the column that contains the totals in every
17th row have other numbers or content inbetween the totals (row 1-16) or
are they all blank? If they are all blank (or blank and text), just sum the
whole column. If there are other numbers in rows 1-16 in that same column,
then one option is to find a text identifier in a different column that
/only/ occurs in the same rows as your totals (such as the word "Total").
Chris was suggesting that you use a formula that checks another column for
some unique word, and then only add up the information in your Totals column
from the rows that contain that unique word.

If the other cells in your totals column contain numbers /and/ there are no
unique identifiers on the totals rows (in other columns), then there are
probably several ways to do this- I'd be inclined to use an array formula.
You enter an array formula by entering the formula, then with the cursor
inside the cell, press Ctl-Shift-Enter. It is entered properly if the
formula (when you click back on the cell) has curly brackets { } around it.

Here is a working sample formula that you can use as a starting point.
Create a new worksheet and put some numbers in Column C, maybe rows 4
through 25. Then put this formula in a cell in another column (doesn't
matter where) and press Ctl-Shift-Enter. It should give you a total of the
numbers in cells 7,14,21, and so on (every 7 cells).

=SUM(IF(MOD(ROW(C4:C10000),7)=0,C4:C10000,0))

In your actual data worksheet you will need to modify the 7 to be the
correct number of rows, the column C to whatever column you need. Your end
result might look more like:

=SUM(IF(MOD(ROW(H4:H10000),17)=0,H4:H10000,0))

You may need to tell the formula what cell to start in, if the first total
is not actually in row 17 (e.g. if you have extra rows at the top of your
data, maybe containing information about the report or something). The
following change tells it that your first total is actually on row 19, and
every total after that is still 17 rows apart.

=SUM(IF(MOD(ROW(H4:H10000)-2,17)=0,H4:H10000,0))

Best of luck,
Keith


"Jack_Feeman" wrote in message
...
Thanks for the really quick reply Chris.
I will try your suggestion. I may have badly worded my question, here is a
better description:
"I have a 3000+ row spreadsheet with totals in the same column every 17th
row."
Would this clarification change your suggestion?

Thanks Jack

"cht13er" wrote:

On Mar 27, 9:04 am, Jack_Feeman
wrote:
Excell 2003
I have a 3000+ line spreadsheet with totals in one column every 17th
row.
What formula can I use to sum these totals?

Thanks
Jack


If the rest of the rows in that column are empty, you can just sum

Else, a =SUMIF( would work well if there was some way you could
differentiate those rows with a sum ... for example, if the word "sum"
was in a previous column you could use something like this:
=SUMIF(D1:E15,"sum",E1:E15)

HTH

Chris





  #6   Report Post  
Old March 27th 08, 02:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 100
Default Formula to sum non-adjacent cells

Heh, I like that solution even better 8-)
Keith

"cht13er" wrote in message
...
On Mar 27, 9:26 am, Jack_Feeman
wrote:
Thanks for the really quick reply Chris.
I will try your suggestion. I may have badly worded my question, here is a
better description:
"I have a 3000+ row spreadsheet with totals in the same column every 17th
row."
Would this clarification change your suggestion?

Thanks Jack



"cht13er" wrote:
On Mar 27, 9:04 am, Jack_Feeman
wrote:
Excell 2003
I have a 3000+ line spreadsheet with totals in one column every 17th
row.
What formula can I use to sum these totals?


Thanks
Jack


If the rest of the rows in that column are empty, you can just sum


Else, a =SUMIF( would work well if there was some way you could
differentiate those rows with a sum ... for example, if the word "sum"
was in a previous column you could use something like this:
=SUMIF(D1:E15,"sum",E1:E15)


HTH


Chris- Hide quoted text -


- Show quoted text -


So it's like
1
1
1
1
1
1
1
1
1
1
1
16
1
1
1
1
1
1
1
1
1
1
1
1
1
16

?

If so, the sum of the entire column is twice that of just the "every
17 rows" ...

Chris


  #7   Report Post  
Old March 27th 08, 03:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 47
Default Formula to sum non-adjacent cells

Keith and Chris,

I really appreciate your quick and specific responses. I will try your
suggestions based on your assumptions. I will let you know either way if it
works or not.

"Ker_01" wrote:

Jack-

You may find that you get more responses and ideas in
microsoft.public.excel.worksheet.functions, which has more of a focus on
worksheet formulas/functions. That said...

I think Chris was indicating that we still don't have enough information to
ensure the best solution. Does the column that contains the totals in every
17th row have other numbers or content inbetween the totals (row 1-16) or
are they all blank? If they are all blank (or blank and text), just sum the
whole column. If there are other numbers in rows 1-16 in that same column,
then one option is to find a text identifier in a different column that
/only/ occurs in the same rows as your totals (such as the word "Total").
Chris was suggesting that you use a formula that checks another column for
some unique word, and then only add up the information in your Totals column
from the rows that contain that unique word.

If the other cells in your totals column contain numbers /and/ there are no
unique identifiers on the totals rows (in other columns), then there are
probably several ways to do this- I'd be inclined to use an array formula.
You enter an array formula by entering the formula, then with the cursor
inside the cell, press Ctl-Shift-Enter. It is entered properly if the
formula (when you click back on the cell) has curly brackets { } around it.

Here is a working sample formula that you can use as a starting point.
Create a new worksheet and put some numbers in Column C, maybe rows 4
through 25. Then put this formula in a cell in another column (doesn't
matter where) and press Ctl-Shift-Enter. It should give you a total of the
numbers in cells 7,14,21, and so on (every 7 cells).

=SUM(IF(MOD(ROW(C4:C10000),7)=0,C4:C10000,0))

In your actual data worksheet you will need to modify the 7 to be the
correct number of rows, the column C to whatever column you need. Your end
result might look more like:

=SUM(IF(MOD(ROW(H4:H10000),17)=0,H4:H10000,0))

You may need to tell the formula what cell to start in, if the first total
is not actually in row 17 (e.g. if you have extra rows at the top of your
data, maybe containing information about the report or something). The
following change tells it that your first total is actually on row 19, and
every total after that is still 17 rows apart.

=SUM(IF(MOD(ROW(H4:H10000)-2,17)=0,H4:H10000,0))

Best of luck,
Keith


"Jack_Feeman" wrote in message
...
Thanks for the really quick reply Chris.
I will try your suggestion. I may have badly worded my question, here is a
better description:
"I have a 3000+ row spreadsheet with totals in the same column every 17th
row."
Would this clarification change your suggestion?

Thanks Jack

"cht13er" wrote:

On Mar 27, 9:04 am, Jack_Feeman
wrote:
Excell 2003
I have a 3000+ line spreadsheet with totals in one column every 17th
row.
What formula can I use to sum these totals?

Thanks
Jack

If the rest of the rows in that column are empty, you can just sum

Else, a =SUMIF( would work well if there was some way you could
differentiate those rows with a sum ... for example, if the word "sum"
was in a previous column you could use something like this:
=SUMIF(D1:E15,"sum",E1:E15)

HTH

Chris




  #8   Report Post  
Old March 28th 08, 08:49 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 15
Default Formula to sum non-adjacent cells

Hi Jack,

Supose you data is in column A, you could add in column B this formula :
=MOD(ROW(A1),17)
Fill down

And sum whre the result = 0
=SUMIF(B:B,0,A:A)

HTH
Regards
Jean-Yves

"Jack_Feeman" wrote in message
...
Excell 2003
I have a 3000+ line spreadsheet with totals in one column every 17th row.
What formula can I use to sum these totals?

Thanks
Jack






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula omits adjacent cells Ali Excel Discussion (Misc queries) 2 March 22nd 09 06:19 AM
Formula Omits Adjacent Cells Excel Hater Excel Worksheet Functions 7 November 6th 07 09:15 PM
copying adjacent cells with formula tanner Excel Worksheet Functions 4 May 15th 06 02:50 PM
copying formula into non-adjacent cells, EXCEL2003 jacob Excel Discussion (Misc queries) 1 July 1st 05 12:35 AM
How can I add non-adjacent cells with a formula dhodges00 Excel Worksheet Functions 3 January 12th 05 04:46 PM


All times are GMT +1. The time now is 06:18 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017