Narrow range in Sumif formula

[Steve]

Hi All. I have a workbook with 40K lines of data. In column A are dates.
The dates are sorted and occur more than once. I need to run a SumIf off of
the dates. SImple enough, except it takes my machine 20 minutes to
recalculate every time a cell is modified - my guess is becasue it is
reading all 40K lines again. So, I was hoping to modify the formula to only
run the Sumif on the specific date I wanted. Since the data is sorted by
date, I though I would use a nested offset function:

=OFFSET(INDIRECT(ADDRESS(MATCH($G$1,$A:$A,0),1)),0,0,COUNTIF($A:$A,$G$1))

I would then take theabove formula and use that as the Range in the Sumif
formula. So with this, I was hoping this would shorten the range the Sumif
is looking in. But obviously id is not working.

So for example, I need to Sum all 3/19/2002 data. Rather than reading all
40k rows, I would like to tell the Sumif that the range to look in is
A23334:A45654.

Any ideas? Thanks!!

 

[Frank Kabel]

Hi Steve

one easy solution could be to change the calculation method from
'Automatic' to 'Manual' (see Tools - Options). Now you can change your
data and re-calculate by pressing F9

maybe this helps (without changing your formulas)
Frank

 

[Mark Graesser]

Steve,
I put together a table with 40000 dates in column A and the number 1 in column B. My SUMIF ran instantaneously.

By any chance are the 40000 entries in the table populated by formulas. If you change any cell on a sheet, Excel will recalculate every formula.

Maybe after you create the table you could Copy and Paste_Special>Values to get rid of all the formulas.

Good Luck,
Mark Graesser
(e-mail address removed)
----- Steve wrote: -----

Hi All. I have a workbook with 40K lines of data. In column A are dates.
The dates are sorted and occur more than once. I need to run a SumIf off of
the dates. SImple enough, except it takes my machine 20 minutes to
recalculate every time a cell is modified - my guess is becasue it is
reading all 40K lines again. So, I was hoping to modify the formula to only
run the Sumif on the specific date I wanted. Since the data is sorted by
date, I though I would use a nested offset function:

=OFFSET(INDIRECT(ADDRESS(MATCH($G$1,$A:$A,0),1)),0,0,COUNTIF($A:$A,$G$1))

I would then take theabove formula and use that as the Range in the Sumif
formula. So with this, I was hoping this would shorten the range the Sumif
is looking in. But obviously id is not working.

So for example, I need to Sum all 3/19/2002 data. Rather than reading all
40k rows, I would like to tell the Sumif that the range to look in is
A23334:A45654.

Any ideas? Thanks!!