Summing every 12 rows

I've got 180 rows (these are months).  I want to sume 
every 12 rows and make rows that has this sum to 
represent a year...how would I do that?
0
axwack1 (1)
3/7/2004 6:49:40 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
566 Views

Similar Articles

[PageSpeed] 47

Hi Vincent,

Do you mean every 12th row

=SUMPRODUCT(--(A1:A100)*(MOD(ROW(A1:A100), 12)=0))

or 1-12 for Jan, 13-24 for Feb, then

=SUMPRODUCT((A1:A180)*(ROW(A1:A180)>(MONTH(I1)-1)*12)*(ROW(A1:A180)<=(MONTH(
I1)*12)))

where I1 holds a date for the month to be totalled

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Vincent Lee" <axwack@optonline.net> wrote in message
news:0c7601c40474$f2b78f50$3501280a@phx.gbl...
> I've got 180 rows (these are months).  I want to sume
> every 12 rows and make rows that has this sum to
> represent a year...how would I do that?


0
bob.phillips1 (6510)
3/7/2004 7:25:24 PM
assume data in column a starting in row 1

in row 1 put in
=IF(MOD(ROW(),12)=0,SUM(OFFSET(A1,-11,0,12,1)),"")

drag fill down the column.

-- 
Regards,
Tom Ogilvy

"Vincent Lee" <axwack@optonline.net> wrote in message
news:0c7601c40474$f2b78f50$3501280a@phx.gbl...
> I've got 180 rows (these are months).  I want to sume
> every 12 rows and make rows that has this sum to
> represent a year...how would I do that?


0
twogilvy (1078)
3/7/2004 7:36:45 PM
Reply:

Similar Artilces:

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

Have two of every customer in database!! HOW DO I CHANGE??
For Some reason when i created a new database for our new location it created two of every customer. Anybody have any thoughts cuz i sure as hell can't figure out how to erase the copied customers. The only solution I can offer is to manually go in and delete a copy of each customer. My question is : Did the copy get the same account number and if so how? -- Elizabeth M. "LEGENDSBOSS" <SNOWJOE6910@HOTMAIL.COM> wrote in message news:2a07501c46612$97715d10$a501280a@phx.gbl... > For Some reason when i created a new database for our new > location it created two of...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

Replace variable row number in formulas
What I'm looking for is a macro that will replace the row number in a formula with a new number that I designate through an input box. Below is the code I have thus far. The problem lies in how I'm defining the integer portion of the formula that I want to replace - vbInteger (or vbLong) don't seem to work. Then finally I need to set Section 2 in a loop through Column CN. Any advice would be greatly appreciated! --------------------------------------------------------------------------------------------------------------------------- Sub UpdateFormulas_2() Dim LRowNumber ...

Junk Mail #12
I am using Outlook 2003 and periodically I get the notice that Outlook has downloaded a message that appears to be Junk E-mail and that it was automatically moved to the Junk E-mail folder but when I open the Junk E-mail folder, nothing is there. Why am I getting this message? I am also using Qurb and suspect that messages being quarantined are the same ones that would have gone into the Junk E-mail folder. it's probably a timing issue larry. Outlook applies it's rules and filters and then Qurb respectfully waits till she's finished. So, as Outlook informs you of her in...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

hiding rows
Hi, I want to use the conditional formating of a certain range of cells so that: if a cell in that range is empty (nothing is shown) hide the entire row. note: the values in the cells are copied from other cells in a different worksheet. If this is not possible with conditional formating can it be done with VBA and How? Not possible with CF Look here for a few ways http://www.rondebruin.nl/print.htm#Hide For example Hide Empty rows, Print and unhide the rows This example will loop through row 1:30 in "Sheet1" If every cell in column A:G is empty it will hide that row. Afte...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Money 2004 #12
On the account list in money it will not show subtotals and on the final total is has # # err # #. After each account it has the final tolal on each account and they are all the same. Pleas some one help. Thanks Bob ...

Fill the form with 10 rows
I have 3 columns and each columns has 30 cells. Let's say they are in A1:C30. Now I want to copy them to a new table which has only 10 rows. So I want to copy A1:C10 and paste them in D1:F10, A11:C20 to G1:I10, and A21:C30 to J1:L10. Actually I have a huge database that have several hundreds records. Is there any shortcut to do this? Thanks. Plumstone Hi Plumstone! If this is something you do on a routine basis and the ranges for the copy/paste operation are always the same, the next time you do this, turn on the macro recorder and create a macro. Then, in the future, just run that ma...

plot graph from multiple worksheet as embedded chart object on every worksheet
Hi i want to plot the graph for every worksheet (column B and column D). I have more than 50 worksheets in the same workbook. How do i write a macro to ask the excel to plot the graph for every sheet and present the chart object on the same sheet? thank you very much for your help! i have been working on this over 2 days still cannot figure it out... ...

Deleting multiple rows from value list
Is there a way to delete multiple rows in a value list for a Text column? I have over 200 rows out of a value list that are duplicates that I need to delete. I can delete them row by row but I'd like to click on the 1st row and shift click on the last row that needs to deleted. Is this possible? Thanks in advance (from a Project newbie) Yes. Select then click on the cut icon. Try it. -Jack Dahlgren "Val" <Val@discussions.microsoft.com> wrote in message news:6596A51E-9C60-46A4-B9AB-AAB335A92906@microsoft.com... > Is there a way to delete multiple ro...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

=SUM Ranges Do Not Update
I have a Excel 2000 spread sheet, with the following macro to insert new row. Sub InsertRow() ' ' Macro1 Macro ' Macro recorded 4/27/2004 ' 'GoTo label, MyString ActiveSheet.Unprotect Application.Goto Reference:="MyCell" ActiveCell.Select ActiveCell.EntireRow.Insert ActiveCell.Offset(-1, 0).Select ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAll Application.CutCopyMode = False ActiveCell.Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True End Sub The problem is that in the "Mycell&qu...

Sum value between dates problem driving me mad!
Good Evening All, I am really struggling here, have tried, nested IF's including AND's but am a bit stumped. I am fairly familiar with Arrays, but cannot seem to combine all. I have a data table (as detailed below). (Hopefully, this should be 'pastable' into Excel - it works for me) I simply wish to calculate the expected totals per month. Any help & assistance with this would be most welcome. Cheers, Mathew Note, earliest Start is 01/04/03 Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 01/04/2003 23.00 01/04/2003 09/07/2003 23.00 ...

videos in MSN keep cutting out every couple of seconds
All the MSN videos that come up in the Mail keep cutting out every couple of seconds and it's impossible to watch them - Martin Hall What happens if you save them to your PC first? "marty67" <marty67@discussions.microsoft.com> wrote in message news:0B5176F8-A7D8-4ED8-B22E-C0E52434277F@microsoft.com... > All the MSN videos that come up in the Mail keep cutting out every couple of > seconds and it's impossible to watch them - Martin Hall ...

Conditional Sum Wizard
Currently I am setting up a sheet and I am using the conditional sum wizard for formulas. Once a formula is created the sytem won't let me copy or change a formula. If I try to copy (Copy, paste special) a formula to another cell, the formula won't work anymore. If I create a formula with the wizard and afterwards change one of the parameters, the formula does not work anymore. Can anyone give me a hint on how I can solve these issues? Thanks. Please don't multi-post - you have an answer elsewhere, relating to use of CSE. Pete On Jan 6, 9:23=A0am, MarcoKoenders <MarcoKo...

Is there a way to rotate a column of data into a row of data?
Hi all, Is there a way to rotate a column of data into a row of data, in Excell 2003? Let's say B8 - B1000, there are so many of them... I want to rotate it into a row form... Thanks a lot! You'll need to explain the rules a little better.... You want to convert 992 vertical cells into one row .....but Excel 2003 only has 256 columns. *********** Regards, Ron XL2002, WinXP "cfman" wrote: > Hi all, > > Is there a way to rotate a column of data into a row of data, in Excell > 2003? > > Let's say B8 - B1000, there are so many of them... I...

Formatting cells #12
Something has happened with my cells that has whacked the formatting. I have entered =(sum(E5:W5)/5280)&" ft." It gives me the correct answer but I have 10 too many zeroes after my decimal place. How do I format the cell so that I only have three decimal places??? CHRIS =TEXT(SUM(E5:W5)/5280,"0.000")& ft." On Wed, 20 Oct 2004 14:03:02 -0700, "Christopher Anderson" <ChristopherAnderson@discussions.microsoft.com> wrote: >Something has happened with my cells that has whacked the formatting. > >I have entered =(sum(E5:W5)/5280)...

How to use outline data (grouped rows) in a protected worksheet?
I want to protect a sheet containing outline data in grouped rows. After protection, users cannot hide or display the rows using the expansion buttons in the margin. Is using outline data impossible in protected sheets? ...

Sum Question
I am using Excel 2000 I have the following very simple formula in column j Sheet1 =SUM(B5*H5) I have this copied all the way down to line 40. It shows 0 in all of the cells all the way down. I would like for there to be a way that the cell would not show anything in it UNLESS there was something that it was calculating. For example. I have entered data in column b and column h through line 10, but it still shows 0 in column J all the way down to line 40. I realize that it is showing these 0's b/c I have placed the formula there, but is there a way that it will still calculate but only ...

Data in Tables updates every 15 mins
I receive data every 15 mins in an Excel database format in the form of an attachment to an email.. I would like to be able to use this file as a dynamic base file used in an ACCESS file/program. Any ideas on how to get this data in the email attachment into Access every 15-30 mins automatically? You will need to break this task into bite-sized chunks, and conquer each task. I would start by trying out Helen Feddema's sample: Saving Email Attachments to an Access Table (See # 123) http://www.helenfeddema.com/access.htm Hopefully, the structure of t...

Pivot Table calc difference between two rows of data
In a pivot table that has just two rows of data I would like to calculate the difference between the two rows (row1 - row2) for all the columns. One thing I tried was to select a cell in the Grand Total row, right click and select Value Field Settings then enter Custom Name "Diff". Then in the Show Value As tab, select Difference From and "row1" and ok. the data in rwo1 is now blank, row2 is is not what I expect and the grand total is blank. So I apparently dont have a clue about this and need to be pointed in the right direction. Excel 2007 Plot row differences. Two wa...

RMS-12-199
Hi, I am preparing for a Store Operation exam end of this month. I would like to know where to get a sample test paper for the above exam? and Is there any tips to help me pass the exam? Thanks in advance. ...