Narrow the range a Sumif references
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. (By the way, there's a ton of other data
and formulas in this sheet that are also contributing to the
slowness!) 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 thought 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 the above 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. This formula works when I use it in data validation,
but not within a typical function.
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!!
|