Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif with references on different tabs | Excel Discussion (Misc queries) | |||
Cell references in SUMIF formulas | Excel Worksheet Functions | |||
Narrow Range based on Selection in Another Range | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
SUMIF - 2 conditions - with references | Excel Worksheet Functions |