Count if one condition and one of two other conditions are met

**jessjo** · 06-19-2014, 04:50 PM

Hi there,

I have been working on a formula that will count the number of students who have applied for admissions and have either a 95 or higher or an 18 or higher of two test scores. Some have both the 95+ and the 18+, but I only want the student counted once. Below is the formula I have tried to use (including as an array), but keep getting duplicate counts, etc. Those who have applied for admissions have a number that begins with an "S". Essentially, I want them to have an "S" number in column B and either a 95+ in column D or an 18+ in column F in order to be counted. I would also like to divide the counted number of students by another cell (B9 - the total number of students) to return a percentage (not reflected in the formula below).

Any help would be greatly appreciated. This is driving me nuts!

=SUM(IF((B6:B7="S*")*(D6:D7>=95)+(F6:F7>=18),1,0))

**Richard Buttrey** · 06-19-2014, 05:10 PM

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.