sumproduct

[johnpdavey]

i have two columns of data, each cell contains one letter selected from A to G and U and a few of the cells have #N/A
i want to count the number of times adjacent cells match and are equal to A or B or....
i have tried the formula =SUMPRODUCT((N2:N124="A")*(O2:O124="A")) to count the number of matches for A, but it returns #N/A ( i know there are a couple of matches)
can you please tell me what i have done wrong.
i will want to extend this formula to include results from at least two other columns containing text eg =SUMPRODUCT((N2:N124="A")*(O2:O124="A")*(C2:c124="F")*(F2:F124="turkish")) in order to calculate the number of turkish females that achieved a grade A in both their mock (column N) and actual (column O) exams.

i would also like to determine the number of turkish females that matched or exceeded their mock grade, grades range from A - G and U for fail, with A the highest. can i use some variation of sumproduct, or would it be sumif?
any help gratefuly appreciated
thank you
john

 

[Juan Pablo González]

Since you have a quite hight level of complexity in those formulas, using several conditions (BTW, that first SUMPRODUCT looks fine to me), I would recommend using a Pivot Table instead.

 

[Aladin Akyurek]

If a reference contains an error value, almost any formula that must test that reference will return that error value as result. Why is it that you have #N/A in your reference?

 

[johnpdavey]

thanks Aladin
i hadn't realised that an #N/A in the data set would cause it to be returned from a formula.
i deleted all the #N/A's (they were left over from a previous vlookup operation) and my sumproduct formula worked fine.

any suggestions on how to deal with the turkish females matching or exceeding their mock grade? i'm not sure how a pivot table would do that
john

 

[PaddyD]

How about this:

Book6
	A	B	C	D	E	F
1	ID	Mock	Final	Sameorbetter?	HowMany?
2	FemaleTurkey	U	U	Yes	4
3	FemaleTurkey	A	A	Yes
4	FemaleTurkey	B	C	No
5	FemaleTurkey	C	C	Yes
6	FemaleTurkey	D	C	Yes
7	MaleTurkey	E	C	Yes
8	MaleTurkey	F	G	No
9	MaleTurkey	G	A	Yes
10
Sheet1

uses the fact that the ascii codes for letters increase from a to z...

Paddy

 

[johnpdavey]

thanks paddy
i had a bit of a play with my original sumproduct (without the #N/A in my data) and came up with this which works fine =SUMPRODUCT(($N$2:$N$124=A)*($O$2:$O$124<=A)), i've actual referenced it to a set of cells that have the grades A - G, and filled down, so that i get a count of those matching or exceeding the specific target grade and i will extend it to include the turkish, female elements as well.
thanks guys for your help in pointing me in the right direction
john