+ Reply to Thread
Results 1 to 17 of 17

Using multipel sum ranges in Sumproduct() & countif() in array

Hybrid View

  1. #1
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Using multipel sum ranges in Sumproduct() & countif() in array

    Maybe I'm missing something but could't you just use the below (and dispense with I & J)

    H2: =IF(ISNUMBER($G2),$G2&"-"&1+COUNTIF($G$2:$G$7,">"&$G2),"Fail")
    copied down

    EDIT: perhaps you're saying you want to generate H without need for G, I or J ?
    Last edited by DonkeyOte; 07-02-2009 at 04:39 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Using multipel sum ranges in Sumproduct() & countif() in array

    If I'm correct and you want to dispense with all but A:F and present results in G then perhaps:

    G2:
    =IF(COUNTIF($B2:$F2,"<60"),"Fail",SUM($B2:$F2)&"-"&1+SUMPRODUCT(--(SUM($B2:$F2)<(SUBTOTAL(9,OFFSET($B$2:$F$2,ROW($B$2:$F$7)-ROW($B$2:$F$2),0))*(SUBTOTAL(5,OFFSET($B$2:$F$2,ROW($B$2:$F$7)-ROW($B$2:$F$2),0))>=60)))))
    Note however that this is Volatile Sumproduct and as such performance is not going to be great - you would IMO be best served forgoing elegance for the sake of efficiency and using your existing setup of G and previously provided solution for H.

    You could probably do the above with a FREQUENCY Array but these aren't my strong point - I will take a look though.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1