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