sumproduct

johnpdavey

Board Regular
Joined
May 30, 2002
Messages
88
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
How about this:
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...

Paddy
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top