Help!

[Guest]

I may be being really dumb but I can't get this formula to work. I know in
the cell I've assigned behind the drop down box, a number is selected each
time, I have assigned the relevant number against the relevant class name and
set the range for the formula to search down the numbers, when it finds a
number then it looks to see if the entry in "Religion" matches the cell it is
comparing it to. The problem is I either get #NA or a 0 appearing. What am
I doing wrong?

 

[Guest]

OK, well both of those formulas gave me the correct response, however, the
problem lies with recognising the first criteria which is the class name. On
the sheet a control box with a drop down list of the 12 classes is assigned a
cell link of $c$2, therefore I need the formula to only return religion
results when the class name matches.

My current formula looks like this:

=SUMPRODUCT(--('Main Worksheet'!$B$3:$B$350=Sheet2!$C$2),--('Main
Worksheet'!$I$3:$I$350=Sheet2!D5))

Main worksheet column B is where the class names of each pupil reside,
COlumn I is the religion column. ON sheet 2 C2 is the location of the drop
down box containing the class names and D5 is the religion required. There
are about 15 religions going dowen therefore E5, F5 etc.

THis formula gives the response of #NA. I think it's more likely to be the
problem of the control box possibly??

 

[Guest]

BTW, I know know that it's something to do with my selection box, as if I
type in the class name in C2, I get the right result

 

[Guest]

try
=SUMPRODUCT(--('Main Worksheet'!$B$3:$B$350=trim(Sheet2!$C$2)),--('Main
Worksheet'!$I$3:$I$350=Sheet2!D5))

There may be leading or trailing spaces in your dropdowns

Anothe thing to try is to enter =len(C2) and check if it says the expected
numbers to see if there unseen characters.

 

[Guest]

thanks for your help, I circumvented the problem, by setting up a list, and
validaing the data, so that the user can only enter the actual class names,
or choose them from the drop down list. This seemed to solve the problem!