# Counting cells against conditions in 2 columns

I run Excel 2002 in Windows 7.
I have a sheet which grades many examples of 3 items (called A B and C, say) into 3 grades (1, 2, and 3). Each example gets a new row.
Col A contains the Item name, Cols B-H contain other data, Cols I-O contain tests and Col P contains the calculated grade for that example.
I need to count how many examples of Item A in Col A are graded as a 3 in Col P, etc.
Could someone guide me?
Tony
###### Who is Participating?

x

Commented:
If I'm not mistaken, after reading again you question, this is the formula that you want:

=SUMPRODUCT((A:A="A")*(P:P=3))

It will count how many items "A" you have on column A that have a grade "3" in column P, right?

jppinto
0

sear AWS,

you could use a sumproduct for this.. I will give you the correct formula when you can give me a example.
I run dutch version so paste the formula will not work ...

Kr

Eric
0

Commented:
Could you post a sample data please? It's not clear what you want to do...

jppinto
0

Commented:
If you just want to count the number 3 in column A, you can use a simple COUNTIF():

=COUNTIF(A:A,3)

If you want to count using multiple criterias, you need to use SUMPRODUCT() function, like this example:

http://excel-user.blogspot.com/2009/10/sumproduct-sum-values-based-on.html

If you post a sample data, I can give you a more realistic answer.

jppinto
0

Commented:
In Excel 2002 you need to use a fixed range for SUMPRODUCT (not the whole column) so you'd need to revise jppinto's suggestion something like this

=SUMPRODUCT((A\$2:A\$100="A")*(P\$2:P\$100=3))

extend ranges as required

regards, barry

regards, barry
0

Author Commented:
Thanks very much. Exactly what was needed.  And thanks in particular for reference to the simple article on the use of SUMPRODUCT which, for me anyway, has always been the hardest function to understand.
regards
Tony
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.