Resize Sumif formula parameters for added data
 

2007 (compatibility mode 2003)

Two sections of w/s
Top contains Sumif formulas

Bottom contains detail data which is Sumif'd in the top section

Data has been added to the bottom section.

All of the Sumif formulas contain (except for the Relative "A2"):
=SUMIF($A$68:$A$350,A2,$D$68:$D$350)

The new data range extends to 386

Therefore, the formula needs to be
=SUMIF($A$68:$A$386,A2,$D$68:$D$386)

I could use a Replace but I do not think that it is the smartest approach.
Plus I would have to calculate the number to replace. I have a variable in the code area for the
Row of the last data item.

Thoughts? TIA EagleOne

Resize Sumif formula parameters for added data
 

Hi

you could set up some dynamic ranges.
You say you have a variable which gives the last row number. Lets say
this is held in cell A1
InsertNameDefine
Name rngA
Refers to =$A$68:INDEX($A:$A,$A$1)
Name rngD
Refers to =$D$68:INDEX($D:$D,$A$1)

then
=SUMIF(rngA,A2,rngD)

--
Regards

Roger Govier


wrote in message
...
2007 (compatibility mode 2003)

Two sections of w/s
Top contains Sumif formulas

Bottom contains detail data which is Sumif'd in the top section

Data has been added to the bottom section.

All of the Sumif formulas contain (except for the Relative "A2"):
=SUMIF($A$68:$A$350,A2,$D$68:$D$350)

The new data range extends to 386

Therefore, the formula needs to be
=SUMIF($A$68:$A$386,A2,$D$68:$D$386)

I could use a Replace but I do not think that it is the smartest
approach.
Plus I would have to calculate the number to replace. I have a
variable in the code area for the
Row of the last data item.

Thoughts? TIA EagleOne

Resize Sumif formula parameters for added data
 

Why not juts use a range bigger than you would ever need?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
2007 (compatibility mode 2003)

Two sections of w/s
Top contains Sumif formulas

Bottom contains detail data which is Sumif'd in the top section

Data has been added to the bottom section.

All of the Sumif formulas contain (except for the Relative "A2"):
=SUMIF($A$68:$A$350,A2,$D$68:$D$350)

The new data range extends to 386

Therefore, the formula needs to be
=SUMIF($A$68:$A$386,A2,$D$68:$D$386)

I could use a Replace but I do not think that it is the smartest approach.
Plus I would have to calculate the number to replace. I have a variable
in the code area for the
Row of the last data item.

Thoughts? TIA EagleOne

Resize Sumif formula parameters for added data
 

Hi Bob!

Does that not use more memory and/or computation?

Also, the App may well have rather large possible data items.

"Bob Phillips" wrote:

Why not juts use a range bigger than you would ever need?

Resize Sumif formula parameters for added data
 

Sumif and countif only look at the UsedRange.

so
=Sumif(A:A,1,B:B)

would take no more resources than

=Sumif(A1:A10,1,B1:B10) if the used range ends at row 10. That isn't true
for most other functions. So using $A$68:$A$2000 shouldn't be a
problem.

--
Regards,
Tom Ogilvy


" wrote:

Hi Bob!

Does that not use more memory and/or computation?

Also, the App may well have rather large possible data items.

"Bob Phillips" wrote:

Why not juts use a range bigger than you would ever need?

Resize Sumif formula parameters for added data
 

Roger, I like your idea - use of Index().

Index is an XL function. What is the comparable VBA function?

"Roger Govier" wrote:

Hi

you could set up some dynamic ranges.
You say you have a variable which gives the last row number. Lets say
this is held in cell A1
InsertNameDefine
Name rngA
Refers to =$A$68:INDEX($A:$A,$A$1)
Name rngD
Refers to =$D$68:INDEX($D:$D,$A$1)

then
=SUMIF(rngA,A2,rngD)