Changing an array formula to a regular formula

[bluefish44]

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.

 

[Dave Patton]

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

 

[bosco_yip]

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