Completely stumped for formula

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" <kirbster1973@discussions.microsoft.com> wrote in message
news:04AE249B-05FE-40D6-91F1-5095B145F9BD@microsoft.com...
>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
>> >