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

1. ## 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  Reply With Quote

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

Replace R, C and T with the relevant ranges.  Reply With Quote

3. 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?   Reply With Quote

4. 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.  Reply With Quote

5. 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  Reply With Quote

6. HI,

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

#### Posting Permissions

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