# Thread: Changing an array formula to a regular formula Thanks: 0 Likes: 0

1. ## Changing an array formula to a regular formula

Hi all and thanks in advance...

I have the following array formula which is working as intended

=SUMPRODUCT(((CONTACTS!\$A\$2:\$A\$5000=A2))/COUNTIFS(CONTACTS!\$A\$2:\$A\$5000,CONTACTS!\$A\$2:\$A\$5000&"",CONTACTS!\$R\$2:\$R\$5000,CONTACTS!\$R\$2:\$R\$5000&""))

Problem is it takes way to long to calculate. Is there a way to achieve the same result with a formula that is not an Array? Or a different way? The formula is basically doing a Countif if with 1 added criteria.

2. ## Re: Changing an array formula to a regular formula

A concise example of say 8 rows and an explanation of the calculation that you require would be useful.

3. ## Re: Changing an array formula to a regular formula

Or try this array (Ctrl+Shift+Enter) conditional count unique formula instead :

=SUM(IF(FREQUENCY(IF(CONTACTS!\$A\$2:\$A\$5000=A2,CONTACTS!\$R\$2:\$R\$5000),CONTACTS!\$R\$2:\$R\$5000),1))

This would be much faster than SUMPRODUCT+COUNTIFS function

Regards
Bosco