Excel - Formula help - Sum results Pass or Fail

Asked By Nadin on 04-Mar-08 02:54 PM
Need help with a formula.

Want to sum the total of a range of cells, then if it is less then a
specific number the value should be "Fail", if greater then that number then
value should be "Pass"

I've used the And function True or False results.  But they want the value
in the cells to say either Pass or Fail.

current formula:  =AND(SUM(J8:R8)>0,SUM(J8:R8)<8)


Ron Coderre replied on 04-Mar-08 03:02 PM
Try this:


Is that something you can work with?


Microsoft MVP (Excel)
(XL2003, Win XP)
Nadin replied on 04-Mar-08 03:48 PM
Tried that originally, but at the bottom of the column want a count for all
that say Fail
Problem is when copy the formula down the column it sets all to Fail because
there isn't any data entered yet, so the count value is wrong.  As data will
be entered daily, but there will be blank rows until the entire month is
filled in.   But need the count to reflect current count for any day that
they look at it.  So if the entire column says Fail, the count will be wrong.

Not too sure how to work around that unless I put in default values in all
the rows and columns, not wanting to do that as it will throw off the count
for all the other columns.  And not giving true totals.
Is there a way to leave the pass/fail value blank until it has numbers in
the cells to sum?
Ron Coderre replied on 04-Mar-08 03:52 PM
Perhaps this?
=IF(COUNT(J8:R8),IF(SUM(J8:R8)<=8,"Fail","Pass"),"no data")

Does that help?


Microsoft MVP (Excel)
(XL2003, Win XP)
Nadin replied on 04-Mar-08 04:08 PM
YES  :-)  that did the trick perfectly.  Thank you thank you..  Been at this
for awhile now.  I will be adding this to my list of formula tips.
Ron Coderre replied on 04-Mar-08 04:25 PM
I am so glad I could help, Nadine.


Microsoft MVP (Excel)
(XL2003, Win XP)
Gord Dibben replied on 04-Mar-08 05:11 PM
For a blank look.


Assumes the "Fail", "Pass" formulas are in column A

To count the occurences of Pass or Fail



Gord Dibben  MS Excel MVP

On Tue, 4 Mar 2008 12:48:06 -0800, Nadine <Nadine@>