multiple criteria

  • Thread starter Thread starter David J. Birnbaum
  • Start date Start date
D

David J. Birnbaum

Dear Excel Users,

Can someone advise me on how to count cell values according to multiple
criteria? Here are the details:

I administer a large university course with seven instructors. All
grades for the course are kept in a single spreadsheet, where the rows
are students and the columns are grades and other information about the
students. One column is the instructor's surname.

I want to track grading across instructors. I'd like to do this by
designing a table where the columns are the instructors' surnames and
the rows are the different letter grades. I then want to populate the
cells of the table with the number of A+, A, A-, etc. grades assigned by
each instructor.

I've used COUNTIF to find the number of, say, A+ grades in the entire
grades column, but I don't know how to use the column with the
instructor's surname as an additional criterion so as to extract only
the A+ grades that were assigned by a particular instructor.

Thank you for any advice.

Sincerely,

David Birnbaum
(e-mail address removed)
 
=SUMPRODUCT((Instructors="Jones")*(Grades="A+"))
where you have ranges named Instructors and Grades. Otherwise, substitute
the actual cell references for the named ranges.
 
You can create an array formula. Type in the following
formula and before exiting the field (or more
appropriately to exist the field) hit Cntl-Shift-Enter

=COUNT(IF(A2:A10="Jones",IF(B2:B10="A",0)))

Where Jones is the professor and A is the grade.

You should see the following in the task bar

{=COUNT(IF(A2:A10="Jones",IF(B2:B10="A",0)))}

(note the extra brackets)

Regards,

Steve
 
Back
Top