Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Sum every Third Row
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3
Default Sum every Third Row
I have a spreadsheet that is created via an Access database. I am
inserting a Recordset, then inserting some blank rows after each row
of data with formulas. I now need to SUM every third row, Column C,
starting at Row 8....an example is below. Every ROW that has DIFF:
needs to be summed at the end of the spreadsheet...any ides would be
appreciated...Thanks

User ID Route # Pallet Jacks Plastic Pallets Wood Pallets Blankets I/C
Bags Straps Small Wood Comments
USSFXC 5006 1 12 0 0 2 4 0
Ret:
Diff: 1 12 0 0 2 4 0
USSEME 5008 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSEME 5010 1 9 0 2 1 4 0
Ret:
Diff: 1 9 0 2 1 4 0
USSJXO 5012 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSRMS 5014 1 4 8 0 2 6 0
Ret:
Diff: 1 4 8 0 2 6 0
USSJHZ 5016 1 10 2 0 2 5 0
Ret:
Diff: 1 10 2 0 2 5 0
USSLSD 5024 1 8 4 1 2 4 0
Ret:
Diff: 1 8 4 1 2 4 0
USSAVV 5026 1 11 1 2 0 6 0
Ret:
Diff: 1 11 1 2 0 6 0
USSAVV 5028 1 9 1 0 0 5 0
Ret:
Diff: 1 9 1 0 0 5 0
USSCME 5030 1 11 1 0 2 5 0
Ret:
Diff: 1 11 1 0 2 5 0
USSDOA 5032 1 5 7 0 1 5 0
Ret:
Diff: 1 5 7 0 1 5 0
USSDOA 5034 1 4 8 0 5 4 0
Ret:
Diff: 1 4 8 0 5 4 0
USSBHN 5036 1 8 4 0 0 4 0
Ret:
Diff: 1 8 4 0 0 4 0
USSBHN 5038 1 11 1 0 0 5 0
Ret:
Diff: 1 11 1 0 0 5 0
USSROB 5070 1 0 21 0 4 5 0
Ret:
Diff: 1 0 21 0 4 5 0
USSJXO 5105 1 8 1 0 2 5 0
Ret:
Diff: 1 8 1 0 2 5 0
USSJXO 5107 0 0 0 0 0 0 0
Ret:
Diff: 0 0 0 0 0 0 0
USSEME 5109 1 9 3 0 4 6 0
Ret:
Diff: 1 9 3 0 4 6 0
USSFXC 5111 1 5 7 0 0 4 0
Ret:
Diff: 1 5 7 0 0 4 0
USSJHZ 5113 1 7 5 0 1 5 0
Ret:
Diff: 1 7 5 0 1 5 0

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,268
Default Sum every Third Row
=SUMIF(A2:A1000,"Diff",B2:B1000)


--
Regards,

Peo Sjoblom



"Arlan" wrote in message
oups.com...
I have a spreadsheet that is created via an Access database. I am
inserting a Recordset, then inserting some blank rows after each row
of data with formulas. I now need to SUM every third row, Column C,
starting at Row 8....an example is below. Every ROW that has DIFF:
needs to be summed at the end of the spreadsheet...any ides would be
appreciated...Thanks

User ID Route # Pallet Jacks Plastic Pallets Wood Pallets Blankets I/C
Bags Straps Small Wood Comments
USSFXC 5006 1 12 0 0 2 4 0
Ret:
Diff: 1 12 0 0 2 4 0
USSEME 5008 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSEME 5010 1 9 0 2 1 4 0
Ret:
Diff: 1 9 0 2 1 4 0
USSJXO 5012 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSRMS 5014 1 4 8 0 2 6 0
Ret:
Diff: 1 4 8 0 2 6 0
USSJHZ 5016 1 10 2 0 2 5 0
Ret:
Diff: 1 10 2 0 2 5 0
USSLSD 5024 1 8 4 1 2 4 0
Ret:
Diff: 1 8 4 1 2 4 0
USSAVV 5026 1 11 1 2 0 6 0
Ret:
Diff: 1 11 1 2 0 6 0
USSAVV 5028 1 9 1 0 0 5 0
Ret:
Diff: 1 9 1 0 0 5 0
USSCME 5030 1 11 1 0 2 5 0
Ret:
Diff: 1 11 1 0 2 5 0
USSDOA 5032 1 5 7 0 1 5 0
Ret:
Diff: 1 5 7 0 1 5 0
USSDOA 5034 1 4 8 0 5 4 0
Ret:
Diff: 1 4 8 0 5 4 0
USSBHN 5036 1 8 4 0 0 4 0
Ret:
Diff: 1 8 4 0 0 4 0
USSBHN 5038 1 11 1 0 0 5 0
Ret:
Diff: 1 11 1 0 0 5 0
USSROB 5070 1 0 21 0 4 5 0
Ret:
Diff: 1 0 21 0 4 5 0
USSJXO 5105 1 8 1 0 2 5 0
Ret:
Diff: 1 8 1 0 2 5 0
USSJXO 5107 0 0 0 0 0 0 0
Ret:
Diff: 0 0 0 0 0 0 0
USSEME 5109 1 9 3 0 4 6 0
Ret:
Diff: 1 9 3 0 4 6 0
USSFXC 5111 1 5 7 0 0 4 0
Ret:
Diff: 1 5 7 0 0 4 0
USSJHZ 5113 1 7 5 0 1 5 0
Ret:
Diff: 1 7 5 0 1 5 0



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 527
Default Sum every Third Row
Arlan

Is this what you want

=SUM(IF(MOD(ROW(1:2000),3)=0,c1:c2000)). It will sum every third row in
column C. It is an array formula and must be commited with Ctrl + Shift +
Enter or it will return zero.

Regards
Peter

"Arlan" wrote:

I have a spreadsheet that is created via an Access database. I am
inserting a Recordset, then inserting some blank rows after each row
of data with formulas. I now need to SUM every third row, Column C,
starting at Row 8....an example is below. Every ROW that has DIFF:
needs to be summed at the end of the spreadsheet...any ides would be
appreciated...Thanks

User ID Route # Pallet Jacks Plastic Pallets Wood Pallets Blankets I/C
Bags Straps Small Wood Comments
USSFXC 5006 1 12 0 0 2 4 0
Ret:
Diff: 1 12 0 0 2 4 0
USSEME 5008 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSEME 5010 1 9 0 2 1 4 0
Ret:
Diff: 1 9 0 2 1 4 0
USSJXO 5012 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSRMS 5014 1 4 8 0 2 6 0
Ret:
Diff: 1 4 8 0 2 6 0
USSJHZ 5016 1 10 2 0 2 5 0
Ret:
Diff: 1 10 2 0 2 5 0
USSLSD 5024 1 8 4 1 2 4 0
Ret:
Diff: 1 8 4 1 2 4 0
USSAVV 5026 1 11 1 2 0 6 0
Ret:
Diff: 1 11 1 2 0 6 0
USSAVV 5028 1 9 1 0 0 5 0
Ret:
Diff: 1 9 1 0 0 5 0
USSCME 5030 1 11 1 0 2 5 0
Ret:
Diff: 1 11 1 0 2 5 0
USSDOA 5032 1 5 7 0 1 5 0
Ret:
Diff: 1 5 7 0 1 5 0
USSDOA 5034 1 4 8 0 5 4 0
Ret:
Diff: 1 4 8 0 5 4 0
USSBHN 5036 1 8 4 0 0 4 0
Ret:
Diff: 1 8 4 0 0 4 0
USSBHN 5038 1 11 1 0 0 5 0
Ret:
Diff: 1 11 1 0 0 5 0
USSROB 5070 1 0 21 0 4 5 0
Ret:
Diff: 1 0 21 0 4 5 0
USSJXO 5105 1 8 1 0 2 5 0
Ret:
Diff: 1 8 1 0 2 5 0
USSJXO 5107 0 0 0 0 0 0 0
Ret:
Diff: 0 0 0 0 0 0 0
USSEME 5109 1 9 3 0 4 6 0
Ret:
Diff: 1 9 3 0 4 6 0
USSFXC 5111 1 5 7 0 0 4 0
Ret:
Diff: 1 5 7 0 0 4 0
USSJHZ 5113 1 7 5 0 1 5 0
Ret:
Diff: 1 7 5 0 1 5 0


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,268
Default Sum every Third Row
But it is not necessary if indeed the OP want to sum one column for each
"Diff" in another column?


--
Regards,

Peo Sjoblom



"Billy Liddel" wrote in message
...
Arlan

Is this what you want

=SUM(IF(MOD(ROW(1:2000),3)=0,c1:c2000)). It will sum every third row in
column C. It is an array formula and must be commited with Ctrl + Shift +
Enter or it will return zero.

Regards
Peter

"Arlan" wrote:

I have a spreadsheet that is created via an Access database. I am
inserting a Recordset, then inserting some blank rows after each row
of data with formulas. I now need to SUM every third row, Column C,
starting at Row 8....an example is below. Every ROW that has DIFF:
needs to be summed at the end of the spreadsheet...any ides would be
appreciated...Thanks

User ID Route # Pallet Jacks Plastic Pallets Wood Pallets Blankets I/C
Bags Straps Small Wood Comments
USSFXC 5006 1 12 0 0 2 4 0
Ret:
Diff: 1 12 0 0 2 4 0
USSEME 5008 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSEME 5010 1 9 0 2 1 4 0
Ret:
Diff: 1 9 0 2 1 4 0
USSJXO 5012 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSRMS 5014 1 4 8 0 2 6 0
Ret:
Diff: 1 4 8 0 2 6 0
USSJHZ 5016 1 10 2 0 2 5 0
Ret:
Diff: 1 10 2 0 2 5 0
USSLSD 5024 1 8 4 1 2 4 0
Ret:
Diff: 1 8 4 1 2 4 0
USSAVV 5026 1 11 1 2 0 6 0
Ret:
Diff: 1 11 1 2 0 6 0
USSAVV 5028 1 9 1 0 0 5 0
Ret:
Diff: 1 9 1 0 0 5 0
USSCME 5030 1 11 1 0 2 5 0
Ret:
Diff: 1 11 1 0 2 5 0
USSDOA 5032 1 5 7 0 1 5 0
Ret:
Diff: 1 5 7 0 1 5 0
USSDOA 5034 1 4 8 0 5 4 0
Ret:
Diff: 1 4 8 0 5 4 0
USSBHN 5036 1 8 4 0 0 4 0
Ret:
Diff: 1 8 4 0 0 4 0
USSBHN 5038 1 11 1 0 0 5 0
Ret:
Diff: 1 11 1 0 0 5 0
USSROB 5070 1 0 21 0 4 5 0
Ret:
Diff: 1 0 21 0 4 5 0
USSJXO 5105 1 8 1 0 2 5 0
Ret:
Diff: 1 8 1 0 2 5 0
USSJXO 5107 0 0 0 0 0 0 0
Ret:
Diff: 0 0 0 0 0 0 0
USSEME 5109 1 9 3 0 4 6 0
Ret:
Diff: 1 9 3 0 4 6 0
USSFXC 5111 1 5 7 0 0 4 0
Ret:
Diff: 1 5 7 0 0 4 0
USSJHZ 5113 1 7 5 0 1 5 0
Ret:
Diff: 1 7 5 0 1 5 0




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 527
Default Sum every Third Row
Yes, I thought your solution would probably be correct, but the data looked
as if it might be in one column.

Regards
Peter

"Peo Sjoblom" wrote:

But it is not necessary if indeed the OP want to sum one column for each
"Diff" in another column?


--
Regards,

Peo Sjoblom



"Billy Liddel" wrote in message
...
Arlan

Is this what you want

=SUM(IF(MOD(ROW(1:2000),3)=0,c1:c2000)). It will sum every third row in
column C. It is an array formula and must be commited with Ctrl + Shift +
Enter or it will return zero.

Regards
Peter

"Arlan" wrote:

I have a spreadsheet that is created via an Access database. I am
inserting a Recordset, then inserting some blank rows after each row
of data with formulas. I now need to SUM every third row, Column C,
starting at Row 8....an example is below. Every ROW that has DIFF:
needs to be summed at the end of the spreadsheet...any ides would be
appreciated...Thanks

User ID Route # Pallet Jacks Plastic Pallets Wood Pallets Blankets I/C
Bags Straps Small Wood Comments
USSFXC 5006 1 12 0 0 2 4 0
Ret:
Diff: 1 12 0 0 2 4 0
USSEME 5008 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSEME 5010 1 9 0 2 1 4 0
Ret:
Diff: 1 9 0 2 1 4 0
USSJXO 5012 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSRMS 5014 1 4 8 0 2 6 0
Ret:
Diff: 1 4 8 0 2 6 0
USSJHZ 5016 1 10 2 0 2 5 0
Ret:
Diff: 1 10 2 0 2 5 0
USSLSD 5024 1 8 4 1 2 4 0
Ret:
Diff: 1 8 4 1 2 4 0
USSAVV 5026 1 11 1 2 0 6 0
Ret:
Diff: 1 11 1 2 0 6 0
USSAVV 5028 1 9 1 0 0 5 0
Ret:
Diff: 1 9 1 0 0 5 0
USSCME 5030 1 11 1 0 2 5 0
Ret:
Diff: 1 11 1 0 2 5 0
USSDOA 5032 1 5 7 0 1 5 0
Ret:
Diff: 1 5 7 0 1 5 0
USSDOA 5034 1 4 8 0 5 4 0
Ret:
Diff: 1 4 8 0 5 4 0
USSBHN 5036 1 8 4 0 0 4 0
Ret:
Diff: 1 8 4 0 0 4 0
USSBHN 5038 1 11 1 0 0 5 0
Ret:
Diff: 1 11 1 0 0 5 0
USSROB 5070 1 0 21 0 4 5 0
Ret:
Diff: 1 0 21 0 4 5 0
USSJXO 5105 1 8 1 0 2 5 0
Ret:
Diff: 1 8 1 0 2 5 0
USSJXO 5107 0 0 0 0 0 0 0
Ret:
Diff: 0 0 0 0 0 0 0
USSEME 5109 1 9 3 0 4 6 0
Ret:
Diff: 1 9 3 0 4 6 0
USSFXC 5111 1 5 7 0 0 4 0
Ret:
Diff: 1 5 7 0 0 4 0
USSJHZ 5113 1 7 5 0 1 5 0
Ret:
Diff: 1 7 5 0 1 5 0





Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3
Default Sum every Third Row
On May 12, 5:17 am, Billy Liddel
wrote:
Yes, I thought your solution would probably be correct, but the data looked
as if it might be in one column.

Regards
Peter



"Peo Sjoblom" wrote:
But it is not necessary if indeed the OP want to sum one column for each
"Diff" in another column?

--
Regards,

Peo Sjoblom

"Billy Liddel" wrote in message
...
Arlan

Is this what you want

=SUM(IF(MOD(ROW(1:2000),3)=0,c1:c2000)). It will sum every third row in
column C. It is an array formula and must be commited with Ctrl + Shift +
Enter or it will return zero.

Regards
Peter

"Arlan" wrote:

I have a spreadsheet that is created via an Access database. I am
inserting a Recordset, then inserting some blank rows after each row
of data with formulas. I now need to SUM every third row, Column C,
starting at Row 8....an example is below. Every ROW that has DIFF:
needs to be summed at the end of the spreadsheet...any ides would be
appreciated...Thanks

User ID Route # Pallet Jacks Plastic Pallets Wood Pallets Blankets I/C
Bags Straps Small Wood Comments
USSFXC 5006 1 12 0 0 2 4 0
Ret:
Diff: 1 12 0 0 2 4 0
USSEME 5008 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSEME 5010 1 9 0 2 1 4 0
Ret:
Diff: 1 9 0 2 1 4 0
USSJXO 5012 1 10 2 0 2 6 0
Ret:
Diff: 1 10 2 0 2 6 0
USSRMS 5014 1 4 8 0 2 6 0
Ret:
Diff: 1 4 8 0 2 6 0
USSJHZ 5016 1 10 2 0 2 5 0
Ret:
Diff: 1 10 2 0 2 5 0
USSLSD 5024 1 8 4 1 2 4 0
Ret:
Diff: 1 8 4 1 2 4 0
USSAVV 5026 1 11 1 2 0 6 0
Ret:
Diff: 1 11 1 2 0 6 0
USSAVV 5028 1 9 1 0 0 5 0
Ret:
Diff: 1 9 1 0 0 5 0
USSCME 5030 1 11 1 0 2 5 0
Ret:
Diff: 1 11 1 0 2 5 0
USSDOA 5032 1 5 7 0 1 5 0
Ret:
Diff: 1 5 7 0 1 5 0
USSDOA 5034 1 4 8 0 5 4 0
Ret:
Diff: 1 4 8 0 5 4 0
USSBHN 5036 1 8 4 0 0 4 0
Ret:
Diff: 1 8 4 0 0 4 0
USSBHN 5038 1 11 1 0 0 5 0
Ret:
Diff: 1 11 1 0 0 5 0
USSROB 5070 1 0 21 0 4 5 0
Ret:
Diff: 1 0 21 0 4 5 0
USSJXO 5105 1 8 1 0 2 5 0
Ret:
Diff: 1 8 1 0 2 5 0
USSJXO 5107 0 0 0 0 0 0 0
Ret:
Diff: 0 0 0 0 0 0 0
USSEME 5109 1 9 3 0 4 6 0
Ret:
Diff: 1 9 3 0 4 6 0
USSFXC 5111 1 5 7 0 0 4 0
Ret:
Diff: 1 5 7 0 0 4 0
USSJHZ 5113 1 7 5 0 1 5 0
Ret:
Diff: 1 7 5 0 1 5 0- Hide quoted text -

- Show quoted text -

I will check out your ideas when I get to the office...thanks!

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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


All times are GMT +1. The time now is 04:13 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement