flexinau said:
Is it possible to have a dynamic range formula where you are using an array
i.e. sum(if(A2:A10000="Condition",B2:B10000,""))
When I say dynamic, sometimes instead of looking at row 2 to 10000, I may only want to look at say 2 to 500 rows.
Then more rows you use on an array, the slower Excel is.
The smaller the number of rows, the quicker Excel's processing time.
Thanks
Just to make sure: the formula you posted can be replaced with an ordinary SumIf formula.
Regarding your specific question:
1. If you can specify or compute the To of From:To, you can use idiom like:
{=SUM(IF($A$2:INDEX(A:A,To)="X",IF($B$2:INDEX(B:B,To)=1,$C$2:INDEX(C:C,To))))}
2. If you want to feed the formula the actual size of data which expands/crimps, you might want to opt for creating the so-called dynamic named ranges. For example:
Name: Arange
Definition:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrec)
Name: Brange
Definition:
=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrec)
Name: Crange
Definition:
=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrec)
where Lrec is defined, based on column C with numeric data, as:
=MATCH(9.99999999999999E+307,Sheet1!$C:$C)-ROW(Sheet1!$C$2)+1
Be aware that such dynamic named ranges are also resource-intensive.
The preceeding formula becomes:
{=SUM(IF(Arange="X",IF(Brange=1,Crange)))}
3. If you are on Excel 2003, convert the data range of interest into a list by means of Data|List|Create List.
The 3rd option is the best.