Resize Sumif formula parameters for added data

  • Thread starter Thread starter EagleOne
  • Start date Start date
E

EagleOne

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
 
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
Insert>Name>Define>
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)
 
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)
 
Hi Bob!

Does that not use more memory and/or computation?

Also, the App may well have rather large possible data items.
 
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.
 
Well I learn something every day. Now I understand Bob's answer!

Thanks also for below: ( We were posting out of the thread)


************************************************************************************
do it manually - you will see that when only one cell is selected, then the
special cells command works on the entiresheet.


Possible work around:

Set CurrRng = wks.Range(wks.Cells(DataStartRow, DataTopCol).Address, _
wks.Cells(wks.Cells(Rows.Count, "B").End(xlUp).Row, _
DataTopCol))

Set CurrRng = Intersect(CurrRng,CurrRng.SpecialCells(xlCellTypeVisible))
 
Roger, I like your idea - use of Index().

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