Excel formula riddle

E

egeorge4

I have a spreadsheet containing 150 workorders that were inspected based
on 20 questions(columns). Each question has a specific numerical value
and the only 3 possible answers are: PASS FAIL or N/A. I am trying to
give points (lets say 30) for a pass or N/A, and 0 points for fail.
Resulting in a final score displayed at the end of the row for each
work order based on the 20 questions results. Any suggestions are very
welcomed!
 
E

egeorge4

Thanks Biff! I think that was the ticket. I need to play with it a
little, but it looks like the right formula for what I am trying to
accomplish. Thanks again
!
 
E

egeorge4

Is it possible to enter multiple ranges in this function? I need t
include
R2:T2 AND W2 AND AI2:AH2 etc...
 
B

Biff

Is it possible to enter multiple ranges in this function? I need to
include
R2:T2 AND W2 AND AI2:AH2 etc....
Click to expand...

Nope!

If the range is not contiguous and there is not a specific uniform pattern
to follow, then it's a pita! You can always string a bunch of those
together, one for each separate range:

=SUM(COUNTIF(R2:T2,{"pass","n/a"}),COUNTIF(W2,{"pass","n/a"}),COUNTIF(AH2:AI2,{"pass","n/a"}))*30

Biff
 
E

egeorge4

Thanks again Biff, stringing them together worked fine. Here is my next
roadblock, maybe you can help. The 150 jobs (rows) are divided amoungst
5 companies in column O, lets call them A,B,C,D,E. Column X holds a
status of OVERALL PASS or OVERALL FAIL. How can I show the total number
of OVERALL PASS and OVERALL FAIL for each company,ABD&E??
 
Top