Using IF function across a range of cells

[Dave vd Westhuizen]

Good day,

I wish to do the following but can't seems to get the correct formula

I have 5 cells (eg G16:G20) that contain either a "pass" or "fail" value. I want to add a formula that looks at these cells and if any one of then contains "fail" then the result must be fail else pass.

I tried various IF formulas as well as the Countif formula??

what is the formula for this scenario?
rgds
Dave

[pjwhitfield]

What happened when you tried COUNTIF?

I would have thought

Please Login or Register  to view this content.

would have done the trick?

[Dave vd Westhuizen]

Hi,

thanks - but this formula also doe snot work / give desired result - get a formula error. When I use the countif function - I sometimes get a value error message or else name error message. I think it might be something small, but can't it to work. I will add to reputation once I have it working, ciao

[pjwhitfield]

Can you upload your workbook?

[Dave vd Westhuizen]

done, first time user not sure where is loads to

[JohnTopley]

To attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[pjwhitfield]

If you do a reply but when in there, hit the "Go Advanced" button.

Then click the "Manage Attachments" link then click the "Choose a file" on the first field and once the files been chosen click the Upload button (over to the right)

This will then show the filename in the "Current attachments", scroll to the bottom and select "Close this window"

Then hit Submit Reply and it should be there

[Dave vd Westhuizen]

Thanks, loaded the workbook

[pjwhitfield]

COUNTIF works for me in there?

Please Login or Register  to view this content.

[JohnTopley]

Using the supplied formula would help!

=IF(COUNTIF(G16:G20,"Fail"),"Fail","Pass")

rather than

=IF(G16:G20="Fail","Fail","Pass")

[Dave vd Westhuizen]

Hi, I tried your formula - see doc attached with screen dump of error message

[pjwhitfield]

Do your regional settings mean you should be using ";" instead of ","?

try replacing those

[Dave vd Westhuizen]

Hi
thanks it worked when I replaced all the "," with ";" - appreciate your help

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.