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

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

- Summing a moving range
- Summing specific range-how to sum with spesific range ?
- Sum with moving range
- Multiple Criteria Sum With Dynamic Ranges
- Sum a moving range
- Copy and SUM functions with different ranges
- How to calculate sum of a moving range and average it
- Calculating Moving Range with Blank Cells
- For Each dont move on to Next with union Range
- SUM a variable range with INDEX and MATCH
- Conditional Sum With Dynamic Range
- Sum Digits In Range Of Cells
- Sum With 2 Criteria Across Multiple Sheets
- FORMULA To Sum a moving range automatically- Month, Quarter and YTD results
- Help with dynamic range summation
- Output the sum of a range of numbers in a message box
- Sumif with multiple range & multiple criterias
- Help with expanding Range (please)
- Array formula with defined range from validation list / dropdown box?
- VBA - IF statement to compare the SUM of two ranges
- Count groups of text with multiple ranges
- Excel Sum with multiple (over 2 criteria)
- Using SUM with INDIRECT.EXT
- Moving ranges between worksheets

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

i try with this syntax but it's not work

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

thanks

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

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

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

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

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!

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

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 NextIn 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 NextWhat can I do?

=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!

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.

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

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

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

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"?

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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 SubNow 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.

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

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!

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.

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

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?

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 SubIf 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.