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.