As this formula

=SUM(IF(('Export'!$F$3:$F$5000="Event"),'Export'!$ P$3:$P$5000))

shows, you have a single condition. Therefore a SumIf formula, which is
faster, will suffice:

=SUMIF(Export!$F$3:$F$65536,"Event",Export!$P$3:$P $65536)

If you have more conditions...

Define Lrec as referring to:

=MATCH(REPT("z",255),Export!$F$3:$F$65536)

or as referring to:


=MATCH(9.99999999999999E+307,Export!$P$3:$P$65536)

which you can use in a formula like:

=SUMPRODUCT(--(Export!$F$3:INDEX(Export!$F$3:$F$65536,Lrec)="Eve nt"),...,Export!$P$3:INDEX(Export!$P$3:$P$65536,Lr ec))

If you were on Excel 2003, all this machinery can be avoided.

GerryK wrote:
My data set varies in length and would like my SUM IF formula to always pick
that out. Id like to eliminate going into the formula and changing the 5000.
There are never any blanks.

Im using:

=SUM(IF(('Export'!$F$3:$F$5000="Event"),'Export'!$ P$3:$P$5000))€¦ array
entered.

And can find the last used cell,

=MATCH(REPT("z",255),F:F)€¦ last used cell in column F

but cannot get correct syntax to merge the two formulae.

Any help greatly appreciated.