I repaired the ordering of the responses (so my comments are below), but it
is always a good idea to follow the posting style (top posting or bottom
posting your answers) to match the style of the first responder... that way
people looking the thread up in the future (via the Google archives) will be
able to follow the flow of responses in the order they were posted.

"Salza" wrote in message
...
"Salza" wrote in message

...

Having a problem to get a correct formula for overall passing.

Marks are keyed into the following cells for all the 26 subjects.

Cell F8 = Subject 1
Cell H8-R8 = Subjects 2-7
Cell T8-BE8 = Subject 8-26

To pass the overall exam - a student must pass FIVE subjects (Subject
1, any TWO from Subjects 2-7, and any TWO from Subjects 8-26).

Passing mark for each subject is 36.

Can someone help me with the formula? Thank you.
On May 18, 1:08 am, "Rick Rothstein"

wrote:
I think this does what you want...

=AND(F8<"",COUNTA(H8:R8)1,COUNTA(T8:BE8)1)

but I do note that your H8:R8 and T8:BE8 ranges span more columns that
there
are subject to fill them... note sure how, or if, that affects the
formula.

--
Rick (MVP - Excel)


Thanks a lot, Rick. That works fine. Great help from you,
For every marks, there is a column for grading too. That's why there
are many columns.

What I wasn't sure of was if "extra" entries in the columns (the grading if
I understand you correctly) won't affect the counts for any particular
range). For example, in Columns H through R, if you had a single mark along
with a grade for it, then my formula would return the wrong result... it
would see the mark and its grade as two separate entries to be counted even
though there is only one subject filled in in the range. That was my concern
and the reason I added the part at the end of my post.

--
Rick (MVP - Excel)