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.  Reply With Quote

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.  Reply With Quote

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  Reply With Quote

User Tag List

Tags for this Thread

array, array formula, changing, countif with criteria, formula  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•