# sumproduct

#### johnpdavey

##### Board Regular
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.

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?

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

Book6
ABCDEF
1IDMockFinalSameorbetter?HowMany?
2FemaleTurkeyUUYes4
3FemaleTurkeyAAYes
4FemaleTurkeyBCNo
5FemaleTurkeyCCYes
6FemaleTurkeyDCYes
7MaleTurkeyECYes
8MaleTurkeyFGNo
9MaleTurkeyGAYes
10
Sheet1

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

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

Replies
0
Views
139
Replies
5
Views
738
Replies
3
Views
148
Replies
18
Views
533
Replies
3
Views
195

1,196,409
Messages
6,015,105
Members
441,870
Latest member
kojack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back