Using SUMPRODUCT or COUNTIF to count number of times a value occurs

[Moco123]

Hello

I am quite new to using formula in excel 2007 and I am a bit stuck.
In one column I have the paygrades of employees in the other I have their performance ratings. There are 6 possible paygrades and 9 possible ratings.
I would like to know how many people in two different paygrades received each rating.
I can do this with just one paygrade using the array formula below but I can't seem to find anything that will do it for more than one paygrades together.

So if someone at grade 1 receives a score of 1 this formula will tell me how many people that is true for

=SUMPRODUCT(('name of worksheet'!range grades are scored in=grade)*('name of worksheet'!cell range that ratings are scored in=rating))


E.g.

Grade Rating
13 2
12 1
15 2
13 2
15 5
13 6
12 2
12 6

to tell me that for grades 12 and 13 two people achieved a '6' rating

Any ideas?

[Ron Coderre]

With Sheet1!A1:B9 containing your sample data
AND on Sheet2

Please Login or Register  to view this content.

This regular formula returns the count of records with a Grade of 12 or 13 and a Rating of 6

Please Login or Register  to view this content.

Is that something you can work with?

[Moco123]

Thank you so much that works perfectly!