Speeding up distinct count formula

[powerpackinduo]

I have the following formula that counts distinct alphanumeric records (Sheet2 column A) based on a store (store is column B).
The formula works correctly but because there are so many records it takes forever to process. Is there a more efficient/faster way to do this?

=SUMPRODUCT((Sheet2!$A2:$A40000<>"")/COUNTIF(Sheet2!$A2:$A40000,Sheet1!$A2:$A40000&""),--(Sheet2!$B2:$B40000=Sheet1!B2))

 

[DonkeyOte]

FWIW the formula you're using is presently flawed I'm afraid - should you have

X Y
X Z

and you search based on field 2 being Y your SUMPRODUCT would return 0.5 for X rather than 1 - to be accurate you would need to add a MATCH to your function, ie:

=SUMPRODUCT(--(MATCH(Sheet2!$A$2:$A$40000&":"&Sheet2!$B$2:$B40000,Sheet2!$A$2:$A$40000&":"&Sheet2!$B$2:$B$40000,0)=(ROW(Sheet2!$A$2:$A$40000)-ROW(Sheet2!$A$2)+1)),--(Sheet1!$B$2:$B$40000=Sheet1!B2))

which is obviously even more hideous and inefficient (you could also use a Frequency Array)

A quicker way to calculate this would be to use "helpers" on Sheet2, ie

Sheet2!C1: =$A2&":"&$B2
Sheet2!D1: =1-ISNUMBER(MATCH($C2,$C$1:$C1,0))
copied down

At which point your unique calculation becomes:

Sheet1!cell: 
=SUMIF(Sheet2!$B:$B,Sheet1!B2,Sheet2!$D:$D)

If the data on Sheet2 were sorted appropriately you could no doubt improve performance further (avoiding use of exact match in MATCH function for ex.)

Others may have better ideas for you.

 

[Fazza]

If a non-formula approach is OK, what about using a pivot table, or a query table, or (via VBA) a recordset?

 

[powerpackinduo]

I'd love to and that would be easy, but this solution needs to be a formula.

 

[powerpackinduo]

Donk- I'll look into your method, but I'm not sure if helper cells will be OK for this solution.