Excel formula riddle

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!

Hi!

Try this:

=SUM(COUNTIF(B1:H1,{"pass","n/a"}))*30

Biff

"egeorge4" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> egeorge4
> ------------------------------------------------------------------------
> egeorge4's Profile:
> http://www.excelforum.com/member.php...o&userid=30259
> View this thread: http://www.excelforum.com/showthread...hreadid=499273
>

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
!

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

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

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

"egeorge4" <[email protected]> wrote in
message news:[email protected]...
>
> Is it possible to enter multiple ranges in this function? I need to
> include
> R2:T2 AND W2 AND AI2:AH2 etc....
>
>
> --
> egeorge4
> ------------------------------------------------------------------------
> egeorge4's Profile:
> http://www.excelforum.com/member.php...o&userid=30259
> View this thread: http://www.excelforum.com/showthread...hreadid=499273
>

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??

Take a look at this screencap:

http://img293.imageshack.us/img293/6729/sump4dx.jpg

Enter the formula in cell AB2 and copy across to AC2. Then select both AB2
and AC2 then copy down to AC6.

Biff

"egeorge4" <[email protected]> wrote in
message news:[email protected]...
>
> 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??
>
>
> --
> egeorge4
> ------------------------------------------------------------------------
> egeorge4's Profile:
> http://www.excelforum.com/member.php...o&userid=30259
> View this thread: http://www.excelforum.com/showthread...hreadid=499273
>