On Mon, 6 Jul 2009 08:24:58 +0100, jhumphreys
wrote:


Hi there
I need to use a sum function that only happens if a certain condition
exist, this condition being that every number in a column has to be over
a certain level, here is the scenario:

Students have to take a series of 5 parts of an examination tests, each
part is assessed and a score awarded, the scores for all 5 are summed
and an overall mark awarded for their performance. The condition is
though that they HAVE to have acheived a pass in each test, If they fail
in at least one text, it doesn't sum or just returns a zero result.

Anyone suggest how to do this?

Thanks
JH


If your scores are in cells A1 to A5 and your levels that the scores
have to be over are in cells B1 to B5, then try the following formula:

=AND(A1:A5B1:B5)*SUM(A1:A5)

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

If the limits are the same for each test you can use

=AND(A1:A5B1)*SUM(A1:A5)

instead. The common limit for all tests is put in B1 (or directly into
the formula).

Hope this helps / Lars-Åke