Change Sumproduct to countif(and(

[Dave69rock]

Hi

I'm using this formula

Please Login or Register  to view this content.

As i'm using it over a large spreadsheet that has lots of data it takes a long time to update.

Can this be changed to a countif(and( formula

Dave

[DonkeyOte]

A Count CSE array would be as slow (if not more so) than a Sumproduct.

To speed things up (significantly) use Concatenation and remove need for Array... to do this, based entirely on your sample formula:

Rota!Z6: =E6&":"&H6
copied to Rota!Z206

Your Sumproduct can thus become a standard COUNTIF

=COUNTIF(Rota!$Z6:$Z206,$F1&":"&$G1)

The helper cells have a virtually non-existent computation time and the COUNTIF will be far, far, far quicker than an array.

I hope that helps.

[Dave69rock]

That works much better

Thanks