I can't seem to find an answer to this question:

I have the following lists in excel - names of poeple, what level of qualification they have, which village they live in.

I want to make a table of numbers of people from a certain place with a certain qualification, and have been trying to use AND IF.

What I need to do is say IF in a certain range in one column ( for example E2:E300) , the name of the place is "x" then count how many times the qualification "Y" appears another column ( say G2:G300).

Is this possible ?

thanks

stella herbert

Several ways to approach this. If you want a formula with more than one condition, then use:

=SUMPRODUCT(($E$3:$E$300="x")*($G$3:$G$300="y"))

Another approach is to use the Pivot Table, and rather than sums, use Count.

thanks for your reply, but I have tried this and it doesn't seem to work.

Also tried pivot tables - a bit beyond me !

stella h

I used that formula above with your guidelines and worked fine.

What does the cell show when you use the sumproduct?

Everything comes out at 0. I've double checked that the formula is enterd correctly, can't see anythign wrong with it.

stella

Okay, I assume you are working on a backup copy. A few questions:

The value that you are looking for in column E is text, correct? And likewise, the value in column G is text? (If they are numbers, then omit the " marks in the formula.)

Where do you have this formula? (Should not be in column E or G

I noticed in my example I started at E3 instead of E2, so also G3 rather than G2. But that still should work.

What happens if you change one of the values in column G to y? Does the result of the formula change?

Ok all it needed was for me to drop the " with the number in column G.

Thanks a lot for your time and patience,

much appreciated,

stella

Great! Glad it is working for you!!!

And thanks for getting back to the board with the resolution. That helps everyone!