Results 1 to 6 of 6

Thread: What formula i should be use for this condition?

  1. #1
    Neophyte Ivy's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016

    What formula i should be use for this condition?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    HI,


    i have a list of data:
    C T R
    u9 8 PASS
    u9 8 PASS
    u9 8 PASS
    u13 5 FAIL
    u13 5 PASS


    i want to divide the quantity of "PASS" from R by T for only "u9".
    Example, 3 PASS for u9 divide by 8.
    Any formula can be used on this case. Because i only want to detect 1 "8" from u9 and divide the PASS which belongs to according C.


    Thanks

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,721
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    How about this?

    =COUNTIFS(R,"PASS",C,"u9")/INDEX(T,MATCH(C2,C,0))

    Replace R, C and T with the relevant ranges.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte Ivy's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Hi AliGW,

    Okay, thanks. but if i want the "C" column can automatically count for the next different value, what formula should i used?
    excample, after u9, the next "C" will goes to u13, how is it to detect?

  4. #4
    Neophyte Ivy's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016
    hi,

    here is the exact worksheet.

    - If "DRIVER" or "RECEIVER" passed, then is count as "PASS"
    - total "PASS" for each "COMPONENTS" will need to divide by "TOTALPINS" of each components.

    Thanks.
    Attached Files Attached Files

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean?

    =IF($A2<>$A1,SUMPRODUCT(($A$2:$A$10=$A2)*($D$2:$E$10="PASSED"))/$C2,"")

    copied down

    or if you want to count 1 if either D or E has "PASSED", then try:

    =IF(A2<>A1,SUMPRODUCT(($A$2:$A$10=A2)*(MMULT(--($D$2:$E$10="PASSED"),{1;1})>0))/C2,"")

    copied down
    Last edited by NBVC; 2018-09-25 at 01:25 PM.


  6. #6
    Neophyte Ivy's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016
    HI,

    Yes! that is what i want. Thanks alot. Appreciate on your help.

Posting Permissions

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