Improve Formula speed/efficiency

[sbitaxi]

Hello,

I'm working on trying to get a formula working faster and more
efficiently. Right now any change causes the workbook to lag heavily.

The source data is made up of
40561 records, each record is made up of 23 columns of information

The following formula examines the source and extracts the amount of
time each person (staff) spend on a particular initiative, excluding
events that were cancelled.

=SUMPRODUCT(--(Staff=$B12),--(Initiative=D$1),(Duration))-
(SUMPRODUCT(--(Staff=$B12),--(Initiative=D$1),--(Status="canceled"),
(Duration)))

Is there any way that this can be reworked to decrease the processing
time? An alternative formula to evaluate multiple criteria?


Your assistance is greatly appreciated.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(Staff=$B12),--(Initiative=D$1),--(Status<>"canceled"),
(Duration)))

 

[sbitaxi]

Excellent, that looks great! I couldn't figure out how to condense it.

Thank you!!!