Results 1 to 3 of 3

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

  1. #1
    Board Regular
    Join Date
    Apr 2009
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,738
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,931
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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