What formula i should be use for this condition?

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

How about this?

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

Replace R, C and T with the relevant ranges.

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?

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.

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

HI,

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