Counting cells based on values of adjacent cells

[SpookyAlienX]

Hi everyone,

I have a simple file listing a student's name (text field), his average across a series of exam (percentage field), and his score on a state test (number field). I want to figure out:

1) how many students scored above a certain number on the state test that also averaged above a certain percentage on the exams;

2) how many students scored above a certain number on the state test that also averaged below a certain percentage on the exams.

Example:

John 86% 240
Sally 40% 234
Jim 60% 241

If I wanted to know how many students scored above 240 and also averaged above 80%, it should return 1.

If I wanted to know how many students scored above 240 and also averaged below 80%, it should return 1.

Thank you so much for your help!

[martindwilson]

well none of those criteria are met as no student has ABOVE 240 and GREATER than 80%
do you mean Above or = to?
=COUNTIFS(B1:B10,">=0.8",C1:C10,">=240")
and
=COUNTIFS(B1:B10,"<0.8",C1:C10,">=240")

[SpookyAlienX]

Woops! Yes, I meant greater than or equal to.

Thank you! That worked quite like a charm.

EDITED TO ASK:

What if I wanted to specify the criteria further? For example:

All students who scored greater than 70% but less than 80% on the exams AND got a 240 or higher on the state test?

[martindwilson]

id go back to sum product for that
=SUMPRODUCT(--(B1:B10>0.7),--(B1:B10<0.8),--(C1:C10>=240))

[newdoverman]

If you wanted to stick with the COUNTIFS the "between" criteria would be:

Please Login or Register  to view this content.

[SpookyAlienX]

Thank you both so much! This is perfect.

[newdoverman]

We're glad that you found a suitable solution to your problem. Thanks for the feedback.