3 Conditions

I have a problem where I have three conditions. For example: Using this data

Please Login or Register  to view this content.

I want to count the number of times Bob was either the Main or the Assit for jobs with the Status 1.

When I just had two condition I was using this array to show how many time sBob was the Main for jobs with the Status 1.

{=SUMPRODUCT(IF(A2:A6=1,1,0),IF(B2:B6="Bob",1,0))}

But with the new added condition I want the formula to act like this although this does not work with excel:

{=SUMPRODUCT(IF(A2:A6=1,1,0),IF(B2:B6="Bob",1,0) or (IF(C2:C6="Bob",1,0)))}

Sorry if that was confusing....Any ideas?

try
=SUMPRODUCT((A2:A6=1)*(B2:B6="bob")+(C2:C6="bob"))

That seemed to work in that instance but if you try it with this data (I had to change my data for another example) using status 5:

Please Login or Register  to view this content.

If you try your formula =SUMPRODUCT((A2:A6=5)*(B2:B6="bob")+(C2:C6="bob"))

It comes up with the answere of 3 when there should only be 2 because bob was the main or assist on only 2 jobs where the status was 5.

In other words it should only count if the Status is 5 and bob is in either the Main or Assist catagory.

well it was 12:30 a.m. and i'd been in the pub!