Results 1 to 17 of 17

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

Threaded View

  1. #1
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

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

    Hi Excel Gurus,

    After completing almost 106 days I am very thank full Forums experts as because of them I am able to use some formula more effectively.

    Now I've jammed in below problem and almost struggling from last 2 days, At last, I ve got the solution but seeking for some improvement in formula. Hope I'll get some cool solution from you. My problem is :

    1.In G Column I put logic for Fail and Obtained Marks.
    G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")
    2. Now in H column I want use this formula which I obtained from this forum
    H2=SUMPRODUCT((G$2:G$7>G2)/COUNTIF(G$2:G$7,G$2:G$7&""))+1
    To get the position of Students.
    But the text value "fail" in the G2:G7 getting Position No. 1 and i've noticed the reason by using evaluate formula as well.
    3. I got solution by changing "Fail" with 0 by creating column I and then column H put this formula.
    =SUMPRODUCT((I$2:I$7>I2)/COUNTIF(I$2:I$7,I$2:I$7&""))+1
    or
    =IF(I2=0,"Fail",SUMPRODUCT((I$2:I$7>I2)/COUNTIF(I$2:I$7,I$2:I$7&""))+1)
    Now after spending a more time I made this array formula for H2 to control Text Values
    ={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
    or
    ={IF(COUNTIF(B3:F3,">=60")<>5,"Fail",SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G3))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
    And more work to get total marks + position ......
    ={IF(COUNTIF(B2:F2,">=60")<>5,"Fail",SUM(B2:F2)&"-"&SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
    Now I am wounder that is there a way to get rid of Column G ?????
    By replacing (if there is any method) G2, G3, G4 etc. with Sum(B2:F2); Sum(B3:F3);Sum(B4:F4) etc. in below formula
    ={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}

    All the things are briefly described in attached sheet.
    Attached Files Attached Files
    Last edited by mubashir aziz; 07-07-2009 at 06:17 AM. Reason: special thanks DK for its usual cooperation .....
    If this post helps, Please don't 4get to click the star icon located at the bottom left of my Post.

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