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

=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

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 #NAME 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

I'm not sure what you are trying to explain with "assigned behind the drop
down box"

Suggest to set up a trial worksheet......
Forget what you have set up so far.

Use Peo's second formula and add $ signs as follows

=SUMPRODUCT(--(A$2:A$500=D2),--(B$2:B$500=E2))
enter the formula in F2
enter classnumbers in A2 to A20
and religion names in B2 to B20

enter in D2 to D5 the first class number (same number 4 times)
enter in E2 to E5 the religions (there are only four for this example)
enter in D6 to D9 the second class number
enter in E6 to E9 the four religions again (extend the range using the right
hand bottom corner click and drag method)
enter in D10 to D13 the third class number
enter in E10 to E13 the four religions
etc

now extend the formula in F2 down

you will get a complete summary of all religions per class room.

Have fun.
Feed back is appreciated.
Especially if you still have questions.

Greetings from New Zealand
Bill K

"kirbster1973" wrote in message
...
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 #NAME 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