+ Reply to Thread
Results 1 to 10 of 10

Filter and array formula

Hybrid View

  1. 12-20-2011, 10:33 AM #1
    gnoorod
    gnoorod is offline
    Registered User
    Join Date
    12-20-2011
    Location
    Quebec
    MS-Off Ver
    Excel 2003
    Posts
    6

    Question Filter and array formula

    Hi,

    I have a very hard time to find the solution of my problem. If you are able to help, feel free to jump in!

    So, I want to count the unique partner ID based on 2 criterias:

    1. Years (2009, 2010 and 2011)
    2. SP or SA

    and each row in my table is a transaction with those partners.

    So, to achive this, I use an array formula and I think the formula is ok. But, i found a glitch... If I choose:
    Years: 2011
    Type: SP

    The result is not ok, but if I use the filter to change the order of the "year" column, the result will be ok...

    Why the filter change the result of the formula and how I can avoid this?

    Please use my document in the attachement for testing purpose.

    Thanks for your help

    Bye
    Attached Files Attached Files
    Last edited by gnoorod; 12-21-2011 at 09:50 AM.
    Reply With QuoteRegister To Reply
  2. 12-20-2011, 12:17 PM #2
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Need help with filter and array formula

    I see several blank cells in the Year column. Are these rows just ignored?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  3. 12-20-2011, 04:35 PM #3
    gnoorod
    gnoorod is offline
    Registered User
    Join Date
    12-20-2011
    Location
    Quebec
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Need help with filter and array formula

    Hi,

    Yes blank cells should be ignored (the one with no date)

    Regards,
    Reply With QuoteRegister To Reply
  4. 12-20-2011, 04:56 PM #4
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: Filter and array formula

    Assume Partner number always be a valid numbers, if so,

    G7, copy across & down.

    =SUM(SIGN(FREQUENCY(IF($A$11:$A$346=$F7,IF($C$11:$C$346=G$6,$B$11:$B$346)),$B$11:$B$346)))

    Confirmed with CTRL+SHIFT+ENTER.
    Regards,
    Haseeb Avarakkan
    __________________________________
    "Feedback is the breakfast of champions"
    Reply With QuoteRegister To Reply
  5. 12-20-2011, 04:57 PM #5
    gnoorod
    gnoorod is offline
    Registered User
    Join Date
    12-20-2011
    Location
    Quebec
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Filter and array formula

    unfortunately, sometime it will contain letters
    Reply With QuoteRegister To Reply
  6. 12-20-2011, 05:01 PM #6
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: Filter and array formula

    If so, E5,

    =SUM(SIGN(FREQUENCY(IF((A11:A346=B2)*(C11:C346=B3),IF(B11:B346<>"",MATCH(B11:B346,B11:B346,0))),ROW(B11:B346)-ROW(B11)+1)))

    Array Entered.
    Reply With QuoteRegister To Reply
  7. 12-20-2011, 05:13 PM #7
    gnoorod
    gnoorod is offline
    Registered User
    Join Date
    12-20-2011
    Location
    Quebec
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Filter and array formula

    I don't have the right results with your formula

    Exemple:
    Criterias (year: 2011, Type: SP)
    Your formula: 2
    The right result should be: 34

    And this result is achieved with my formula in E5 only if I sort the column "year":

    In french:
    =SOMME(SI(FREQUENCE(SI(($B$11:$B$512<>"")*
    ($A$11:$A$512=B2)*($C$11:$C$512=B3);
    EQUIV($B$11:$B$512;$B$11:$B$512;0));COLONNE(INDIRECT("1:"&COLONNES($B$11:$B$512))))>0;1))


    In english:
    =sum(if(FREQUENCy(if(($B$11:$B$512<>"")*
    ($A$11:$A$512=B2)*($C$11:$C$512=B3),
    match($B$11:$B$512,$B$11:$B$512,0)),COLumn(INDIRECT("1:"&COLumns($B$11:$B$512))))>0,1))

    Regards,
    Reply With QuoteRegister To Reply
  8. 12-20-2011, 05:21 PM #8
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: Filter and array formula

    I got 34. Did you try exactly like I posted?. i have not been included INDIRECT or COLUMN.
    Reply With QuoteRegister To Reply
  9. 12-21-2011, 09:48 AM #9
    gnoorod
    gnoorod is offline
    Registered User
    Join Date
    12-20-2011
    Location
    Quebec
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Filter and array formula

    Perfect!!!

    I made a mistake in the process of the formula translation...

    Thanks a lot!

    Regards,
    Reply With QuoteRegister To Reply
  10. 12-21-2011, 09:52 AM #10
    gnoorod
    gnoorod is offline
    Registered User
    Join Date
    12-20-2011
    Location
    Quebec
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Filter and array formula

    Haseeb

    me again.. are you able to explain this part of your formula?

    "MATCH(B11:B346,B11:B346,0))),ROW( B11:B346)-ROW(B11)+1)))"

    Thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1