Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

sum with moving range

In Excel2000, I have data that changes monthly and I want a sum
function that changes with it. For example, one month it may look like
this:

may
payroll 1000
500
100

I want to sum to be the 1600. The next month it may look like this (
the # of rows are always changing):

june
payroll 200
300
100
100
200
and I want the sum to be 900. I want the sum formula to start like this
(formula is in "A10") =Sum(a9somehow look up the word payroll and
give me the cell one column to the right). It would always adjust by
looking up the word payroll.

Thanks


Post your answer or comment

comments powered by Disqus
Hello,

I need to sum a moving range that is grows, but then is limited in size with only excel functions.

Let's say I have 10 numbers in 10 columns (not rows):510515201050105I want to sum a range of the numbers, but the range can never be larger than 3 columns large. Therefore in the row beneath the outputs are:5 = 55 + 10 = 155 + 10 + 5 = 2010 + 5 + 15 = 305 + 15 + 20 = 40etc
I want to write one function so that if the array grows, I will be able to drag the formula and still be able to do what I want. Please help!

the logic path is:
if ( current column - initial column) < 3
sum 3 columns

if (current column - initial column) > 3
sum latest 3 columns

how to sum with spesific range ?
i try with this syntax but it's not work

ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 1 & "]C:R[-1]C)"

thanks

In Excel2000, I have data that changes monthly and I want a sum
function that changes with it. For example, one month it may look like
this:

may
payroll 1000
500
100

I want to sum to be the 1600. The next month it may look like this (
the # of rows are always changing):

june
payroll 200
300
100
100
200
and I want the sum to be 900. I want the sum formula to start like this
(formula is in "A10") =Sum(a9somehow look up the word payroll and
give me the cell one column to the right). It would always adjust by
looking up the word payroll.

Thanks

I have a sum part of which is $I$4:$I$500.

The 500 part of the sum is constantly being manually changed using edit replace because of a growing amount of data.

In cell A1 ona another worksheet within the workbook I use the Count function to count the number of rows containing data. This cell is named DataCols

Is it possible to combine $I$ with the named Range DataCols?

I have got this far - ="$I$"&INDIRECT("ColNo") which returns 500 if I remove "&I$"& in front of it.

Thanks

Hi,
I've got a problem with a macro / sum fuction

I want to add into a macro a =sum formula.
Following a data extract, the data in month 1 may be 4 rows deep, the next time I run it, ie month 2 could be 10 rows etc so the sum will be in a different cell each time the macro is run.

The macro takes the cursor to the correct cell where the sum formula should be but if I record the sum, it records the number of rows as opposed to the range to sum so it's ok it there's only ever going to be 4 rows extracted but will miss rows where there are more.

Can I get the sum to recognise the range, eg from cell A4 down to the last piece of data, this being a different row count each time ?

Any help would be greatly appreciated.

thanks
Steve

Dear all

I am attaching an excel file looking your help in two areas.
The first one is to copy a code from one cell to folloning cells with
different ranges. Eg. a code form A8 to A9till A15, a code form A25 to A26
till A32 and so on.

The second problem can be solved with a sumif. I have sum values eg from E10
to E15 and i want to have the sum to E16, then form E20 till to E32 to sumup
to E33 and so on.

The problem is that the file is uite big, exported from ERP and has 48000
lines

Any solution to any of the two problems would be great.

Thank you in advance
Manos

Hello all,

A bit long title :D

I am trying to calculate a moving range based on a defined period

Example:

I have 12 data: 1,2,3,4,5,6,7,8,9,10,11,12
And the period is 4 (written in a cell), note that the period can be different for a different set of data

So the calculation that needs to be performed is: (1+2+3+4)+(2+3+4+5)+,...,+(9+10+11+12) divided by (12-4-1)

How can I write a syntax in VBA to perform such functin in one cell?

My failed test syntax is as follows:

Sample data is from D8 to D19
cell containing period value is E7
And the result should be in cell E8

Sub test()
Dim lr0 As Long
lr0 = Worksheets("sheet1").Range("D8").End(xlDown).Row

For i = 8 To lr0 - 8 + 1 - (Worksheets("sheet1").Range("E7") - 1) Step 1
For j = 8 + Worksheets("sheet1").Range("E7") - 1 To lr0 Step 1
Worksheets("sheet1").Cells(8, 5) = Application.Sum(Worksheets("sheet1").Range("D" & i&), Worksheets("sheet1") _
.Range("D" & j&))
Next j
Next i

End Sub

Can anyone assist me on this?
Thanks in advance!

Hello all,

I am attempting to create a control chart for some data, and need an average moving range (MR bar). This would be simple if my data was all together in column A, simply using something like...

=abs(B2-B1)
However, I have blank cells which for all intensive purposes I want to pretend aren't there. I was thinking maybe a conditional statement used in conjunction with a lookup argument may get me there, but I am having trouble.

example data

Data
1.914535
1.803167

2.091054
1.983503

2.014628

1.789016
1.827065
1.86465

1.938139
1.96658

1.878581
1.867826

Using a manual method and the above code and data (copy and pasting into excel) I get:

MR_Bar=0.09453282

I dont know why element dont move to the next with my Range...

ActionRange is a union of range.find() ranges... So if the cells are consecutive, it separates it with ":" not a comma.
It stucks to "A2" and dont move on to "A6"
Dim ActionRange as Range
Set ActionRange = Range("A1:A2,A6")

            For Each DataCell In ActionRange.Cells
                Range("A" & DataCell.Row & ":C" & DataCell.Row).Insert Shift:=xlShiftDown
                With Range("A" & DataCell.Row - 1 & ":C" & DataCell.Row - 1)
                    .MergeCells = True
                    .Value = "Something"
                End With
            Next
In the case there is no consecutive cells in the union. It works fine
Dim ActionRange as Range
Set ActionRange = Range("A1,A3,A6")

            For Each DataCell In ActionRange.Cells
                Range("A" & DataCell.Row & ":C" & DataCell.Row).Insert Shift:=xlShiftDown
                With Range("A" & DataCell.Row - 1 & ":C" & DataCell.Row - 1)
                    .MergeCells = True
                    .Value = "Something"
                End With
            Next
What can I do?

The formula below works perfectly to find the number being matched but I need it to also calculate the SUM of a range with the last cell of that range being the matched cell, in this case TeamStats!F13, and the first cell of the range to be TeamStats!F5.

=IF(C6="","",IF(ISERROR(INDEX(TeamStats!F:F,MATCH($E$1&C6,TeamStats!B$1:B$1271&TeamStats!A$1:A$1271, 0))),"",INDEX(TeamStats!F:F,MATCH($E$1&C6,TeamStats!B$1:B$1271&TeamStats!A$1:A$1271,0))))

Thanks!

I am trying to build a macro to add some formulas/conditional sums, but the task is too complex for me. I've attached my spreadsheet as a small sample, the actual spreadsheet is much larger.

Column A contains the name headings and Column B contains the names, E and F contain source data.

Column C should have the number in corresponding row (C3/E2 ...) divided into the number in the big heading row w/ the sum of the numbers above them (SUM(C3:C10).

I don't know how to define multiple dynamic ranges or where to start.

Thanks in advance.

Hi,

I am trying to sum the digits in a range of cells, as per the attached example.

I modified a formula I found on this site for summing digits in a single cell, to deal with a range, giving this, entered as an array formula.

{=SUM((LEN(A5:A9)-LEN(SUBSTITUTE(A5:A9,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})}

This works for cells in a cloumn, but not for a row or a block.

How could it be modified to deal with a row or block?

Thanks

Dave

Hello,

My question is similar to the thread Count/sum with two criteria from multiple sheets. For example, I would like to use SUM Formula the number of widgets sold by store (Column A) by month (Row 5) for each salesperson (sheets Homer, Marge, and Lisa). I'm using the following SUMPRODUCT equation to achieve the desired results:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$3&"'!A:A"),$A6,INDIRECT("'"&$A$1:$A$3&"'!b:b")))

where my worksheet names are in range A1:A3, the store names are in range A6:A10, and the months are in B5:D5. However, for each month, I have to manually change the column ie. from "'!b:b" to "'!c:c", "'!d:d", etc. In my real worksheet, the columns extend down to ED so to manually change the range would be extremely time consuming. Does anyone know how to do this?

Thanks for your time and happy holidays!

Tesa

Hi all,

I have been struggling to get this to work and would appreciate a push in the right direction.

I am fairly new to my organisation and they have spreadsheets galore for EOM results. These are being overhauled shortly as we update systems but for now I just want to simplify the manual processes that seem unnecessary. I want to make 1 change at the beginning of the workbook to alter the current month setting and then have all sheets and total columns work automatically. In the scenario below, ideally, I would hope I can make it recognise that the Oct column is the current month by altering 1 cell and have the formula read off this. It would return this in the Mth column, return a sum of Oct and the previous 2 columns in the QTR column and then the YTD column would sum all columns in the range. I tried VLOOKUP and a combo of INDEX and MATCH but could not get it working... I am a noob and any help would be excellent!

Thanks!

Current Month:4 Oct
1 2 3 4 TOTALS Product Jul Aug Sep Oct Mth QTR YTD A 1000 1000 1000 1000 1000 3000 4000 B 2000 2000 2000 2000 2000 6000 8000 C 3000 3000 3000 3000 3000 9000 12000 D 4000 4000 4000 4000 4000 12000 16000

Hello,

I have a report with dynamic ranges and I'm trying to subtotal the ranges. Similar to the table below.

AQ AR AS AT 1 Job 1231 2 start 3 11 13 33 4 -1 0 129 5 97 45 96 6 subtotal 7 start 8 45 55 -15 9 0 0 1 10 subtotal

Here is an example equation:
where s should equal "start" and r should equal "subtotal"

=SUM(AR" & Trim(Str(s)) & ":AR" & Trim(Str(r - 1)) & ")

However, when I run the report, the equation will provides me with: =SUM(AR2:AR5) and =SUM(AR2:AR9). How can I get the equation to begin with each "start"?

All I am trying to do as output the sum of a range of numbers in a msgbox. For some reason the math isn't working. The numbers are in the thousands and some are negative. I want the range to be from H1 to whatever the last cell is that has value before a blank row. Here is the code I am trying to work with:


	VB:
	
 SumAccount() 
    Dim balance As Long 
    Dim rng As Range 
    Dim curcell As Object 
    Dim test As Integer 
     
    test = 0 
    balance = 0 
     
    Set rng = Range("h1", Range("h65536").End(xlUp)) 
     
    For Each curcell In Range("H1", rng) 
        If curcell = "" Then 
            test = 1 
        ElseIf test = 0 Then 
             
            balance = CLng(curcell.Offset(2, 0).Value) _ 
            + curcell.Offset(1, 0).Value 
             
        End If 
    Next 
     
    MsgBox balance 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi There,

I tried built in sumif but no use

I have a sheet where i need sum if result with multiple ranges & criteria
See attached sample sheet

coumn C & colum E is range to search in
criteria is April & Airfreight

Result cell (L3) to show total sum for April but only for Airfreight

Thanks in advance for your prompt help

Hello there.
I have a range of cells with moving data in them.
G5,G6, I5,I6, K5,K6, M5,M6, O5,O6, Q5,Q6

Then its the same again for rows 8,9, 11,12, 14,15, 17,18, 20,21, 23,24

Each of those 84 cells have moving data in them.
I have conditional formatting for each cell to change interior color when value is greater than 100.

Now - i also would like an audible alert when this condition is met too.

So far the code is as follows:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        
    Static aboveLimit As Boolean
If 100 < Application.Min(Range("G5:O5")) Then
    If Not (aboveLimit) Then Call sndPlaySound32("C:documents and settings" & Environ("USERNAME") &
"desktopPositionsSoundsBeep.WAV", 0)

    aboveLimit = True
Else
    aboveLimit = False
End If


End Sub
Now the problem is that if i change range to G:G, for example, if any of those cells in column G are NOT above 100 than there will be no beep if any of the other cells in that range meet the above criteria....

So therefore, I need to modify the above code to look at every cell individually instead in the range of (G:G, I:I, K:K, M:M, O:O, Q:Q).

Can anyone help me with this please?
I have tried like almost 50 different things but cant get it to work for me.

Looking forward to anyone's kind help or responses

I have even tried ranging ("G5, I5, K5, M5, O5, Q5, G6, I6, K6, M6, etc, etc, etc") for all 84 cells but it does not seem to apply to each cell individually.
I also tried adding a: for each cell in range : but this just gave me errors...

THanks in advance.

i have a ctrl+shift+enter formula to sum some items from table:
Code:
where:
"kuu" is defined range in table, that contains first criteria
"suund" is defined range in table, that contains second criteria
"sum" is defined range in table, that contains the values to sum
"$A$2" is constant first criteria
"b$1" is constant second criteria

i would like to replace the "sum" argument with linkt to validation list output of different defined ranges to select

if i replace the "sum" with the output cell aadress of validation list, for example the absolute address "$A$7" (or the defined range for it), formula gives an #VALUE error ;(

how to put it work?

example workbook: http://www.hot.ee/zammalabe//eq20050522.xls

I have a script that I need a little help with. I want it to compare the sum of one range to the some of another range. Both ranges are columns and don't change (specifically O2:O34 and Q2:Q34).

Basically, I want this:

IF(SUM(O2:O34)>SUM(Q2:Q34)
True = Run Macro 6
False = Continue

I hope that makes sense. Thank you in advance!

Count groups of text with multiple ranges

With range A3:P3 how many groups of three
(any order) occur?
Example Apple Carrot Orange =1

I put the criteria in a range A12:A14 and named it rI. The criteria are Apples, Oranges, Carrots.

=(SUM(IF(COUNTIF(A3:C3,rI)=1,1,0))=3)*1 gives result for first group.

What formula will gives the result across the range?

I can get the result with several brute force
methods.
Putting multiple versions of the above (with a3:C3 shifted) is a bit of a monster.

=(SUM(IF(COUNTIF(A3:C3,rI)=1,1,0))=3)*1+(SUM(IF(COUNTIF(B3:D3,rI)=1,1,0))=3)*1 .. etc

Thanks in advance.

I've been trying to get this to work for several hours and am at my wits
ends. I have a data table with one column of numberical data that i need to
sum based upon three or more critria contained in other columns. The data
represents investments and their market values.

I have named each of the columns with a range name. THe critria as
described by their range names and examples of each are :

Country - USA, Great Britain, Japan, etc.
Currency - USD, EUR, GBP, JPY
Type - Bond, Loan, Equity
long_short - Long, Short

I will do many different formulas once I have one that works. I was able to
get a formula to work with two criteria. I would like a formula that adds
the market values of all securities that are Type-Long, Currency-USD, and
Country-USA.

Any help would be appreciated.

--
Joe D

I am trying to sum up a range of cells in an unopen, external worksheet.
Using INDIRECT.EXT, I have only been able to do so if the row number of the
beginning cell is less than 100. For example,

=SUM(INDIRECT.EXT("'C:[MyBook.xls]MySheet!b99:b704"))

will work. However,

=SUM(INDIRECT.EXT("'C:[MyBook.xls]MySheet!b100:b704"))

will work only for an open worksheet, not a closed one.

Additionally, if the ending cell row is 1000 or more, regardless of the
beginning cell, the formula will not work for a closed workbook.

Does anyone know if this problem can be solved, so that I can use
INDIRECT.EXT with SUM over the full range of a worksheet?

Can anyone explain why the first macro below works but the second does not? I am mystified.
I am trying to write a macro to move ranges between worksheets depending on certain criteria being met. I have attached a workbook with the code in.
Ian

	VB:
	
 Intsppt1() 
     
    Sheets("Sheet1").Range(Cells(1, 7), Cells(1, 11)).Value = Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 5)).Value 
     
End Sub 
 
Sub Intsppt2() 
     
     
    Sheets("Sheet2").Range(Cells(1, 7), Cells(1, 11)).Value = Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 5)).Value 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



No luck finding an answer? You could always try Google.