Improve Calculations Speed

I am using this formula on over 1000 rows and it is slowing down calculations.

Is there any way to make it any more efficient please?

Please Login or Register  to view this content.

I should explain what it is supposed to do:

The first IF is simply to check if there is any data.
The second IF is to check if all of the data appears in an approved list (14 cells, so if it is more or less than 14, return blank)
The second SUMPRODUCT is adding up the data against another list of results

Any advice much appreciated.

Hi,

Do you have Excel 2007 or later? If so you may find SUMIFS() work better than the SUMPRODUCT.

However upload your workbook so that we can see in context. I see your comparing values in a horizontal range with numbers in a vertical list but can't quite see what results are required.

Not really sure what that 1st SP is doing...
=SUMPRODUCT(--(ISNUMBER(MATCH(K4:X4,$C$5397:$C$5724,0))))

When I delete ONE 0730-1430 from column C, it drops from 14 to 12
When I delete ONE 1400-2100 from column C, it drops from 14 to 13
When I delete ONE AL from column C, it drops from 14 to 13
When I delete all of those 3 criteria from column C, it drops from 14 to 9

the 0730- and AL 7 seem to be doubled up in your counting anyway?

What exactly are you trying to do here?

=SUM(COUNTIF($C$5397:$C$5909,{"1400-2100","0730-1430","AL 7"}))
this gives you 3

Have you considered using Data Validation to make sure only times you have are allowed inteh 1st place?

Hi,

SUMPRODUCT() functions are notoriously slow when large ranges are involved. It's a pity you don't have Excel 2007 or later when you could have used the much more efficient SUMIFS of COUNTIFS

I'd be inclined to add a parallel set of columns and on each row enter the formula

Formula:

Please Login or Register  to view this content.

then copy this across 14 columns. Then the column Y total can be simply the sum of the 14 new columns. This should speed things up.