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?

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500="Buddist"))

where A2:A500 are the classes, D2 the cell with the dropdown, B2:B500 are
the religions

You could also write it as

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500=E2))

where E2 would hold the religion, that way you don't need to edit the
formula when changing the religion, adapt to fit your ranges


Regards,

Peo Sjoblom

"kirbster1973" wrote:

I have a database containing details of all the children in my school, In one
column are the classes that each child is in, in another column is their
religion, I need to be able to count how many are of a certain religion in
each particular class. I have a drop down list listing each class. I need a
formula to look at that box, which is assigned to a cell, then identify all
children in that particular class and show the number of buddhists(for
example) in a cell.

Hope I've explained this correctly and clearly

Thanks in advance

What do you get when you enter
=SUMPRODUCT(--(A2:A500=D2))
and
=SUMPRODUCT(--(B2:B500="Buddist"))?

Probably one or both are giving you something different than you expect

"kirbster1973" wrote:

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?

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500="Buddist"))

where A2:A500 are the classes, D2 the cell with the dropdown, B2:B500 are
the religions

You could also write it as

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500=E2))

where E2 would hold the religion, that way you don't need to edit the
formula when changing the religion, adapt to fit your ranges


Regards,

Peo Sjoblom

"kirbster1973" wrote:

I have a database containing details of all the children in my school, In one
column are the classes that each child is in, in another column is their
religion, I need to be able to count how many are of a certain religion in
each particular class. I have a drop down list listing each class. I need a
formula to look at that box, which is assigned to a cell, then identify all
children in that particular class and show the number of buddhists(for
example) in a cell.

Hope I've explained this correctly and clearly

Thanks in advance

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??

"bj" wrote:

What do you get when you enter
=SUMPRODUCT(--(A2:A500=D2))
and
=SUMPRODUCT(--(B2:B500="Buddist"))?

Probably one or both are giving you something different than you expect

"kirbster1973" wrote:

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?

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500="Buddist"))

where A2:A500 are the classes, D2 the cell with the dropdown, B2:B500 are
the religions

You could also write it as

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500=E2))

where E2 would hold the religion, that way you don't need to edit the
formula when changing the religion, adapt to fit your ranges


Regards,

Peo Sjoblom

"kirbster1973" wrote:

I have a database containing details of all the children in my school, In one
column are the classes that each child is in, in another column is their
religion, I need to be able to count how many are of a certain religion in
each particular class. I have a drop down list listing each class. I need a
formula to look at that box, which is assigned to a cell, then identify all
children in that particular class and show the number of buddhists(for
example) in a cell.

Hope I've explained this correctly and clearly

Thanks in advance

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

"kirbster1973" wrote:

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??

"bj" wrote:

What do you get when you enter
=SUMPRODUCT(--(A2:A500=D2))
and
=SUMPRODUCT(--(B2:B500="Buddist"))?

Probably one or both are giving you something different than you expect

"kirbster1973" wrote:

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?

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500="Buddist"))

where A2:A500 are the classes, D2 the cell with the dropdown, B2:B500 are
the religions

You could also write it as

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500=E2))

where E2 would hold the religion, that way you don't need to edit the
formula when changing the religion, adapt to fit your ranges


Regards,

Peo Sjoblom

"kirbster1973" wrote:

I have a database containing details of all the children in my school, In one
column are the classes that each child is in, in another column is their
religion, I need to be able to count how many are of a certain religion in
each particular class. I have a drop down list listing each class. I need a
formula to look at that box, which is assigned to a cell, then identify all
children in that particular class and show the number of buddhists(for
example) in a cell.

Hope I've explained this correctly and clearly

Thanks in advance

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.
"kirbster1973" wrote:

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

"kirbster1973" wrote:

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??

"bj" wrote:

What do you get when you enter
=SUMPRODUCT(--(A2:A500=D2))
and
=SUMPRODUCT(--(B2:B500="Buddist"))?

Probably one or both are giving you something different than you expect

"kirbster1973" wrote:

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?

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500="Buddist"))

where A2:A500 are the classes, D2 the cell with the dropdown, B2:B500 are
the religions

You could also write it as

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500=E2))

where E2 would hold the religion, that way you don't need to edit the
formula when changing the religion, adapt to fit your ranges


Regards,

Peo Sjoblom

"kirbster1973" wrote:

I have a database containing details of all the children in my school, In one
column are the classes that each child is in, in another column is their
religion, I need to be able to count how many are of a certain religion in
each particular class. I have a drop down list listing each class. I need a
formula to look at that box, which is assigned to a cell, then identify all
children in that particular class and show the number of buddhists(for
example) in a cell.

Hope I've explained this correctly and clearly

Thanks in advance

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!

"bj" wrote:

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.
"kirbster1973" wrote:

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

"kirbster1973" wrote:

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??

"bj" wrote:

What do you get when you enter
=SUMPRODUCT(--(A2:A500=D2))
and
=SUMPRODUCT(--(B2:B500="Buddist"))?

Probably one or both are giving you something different than you expect

"kirbster1973" wrote:

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?

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500="Buddist"))

where A2:A500 are the classes, D2 the cell with the dropdown, B2:B500 are
the religions

You could also write it as

=SUMPRODUCT(--(A2:A500=D2),--(B2:B500=E2))

where E2 would hold the religion, that way you don't need to edit the
formula when changing the religion, adapt to fit your ranges


Regards,

Peo Sjoblom

"kirbster1973" wrote:

I have a database containing details of all the children in my school, In one
column are the classes that each child is in, in another column is their
religion, I need to be able to count how many are of a certain religion in
each particular class. I have a drop down list listing each class. I need a
formula to look at that box, which is assigned to a cell, then identify all
children in that particular class and show the number of buddhists(for
example) in a cell.

Hope I've explained this correctly and clearly

Thanks in advance