# Thread: Excel using IF and

1. Registered User
Join Date
Jan 2004
Location
Zambia
Posts
4

## Unanswered: Excel using IF and

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

2. Registered User
Join Date
Oct 2003
Posts
1,091
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.

3. Registered User
Join Date
Jan 2004
Location
Zambia
Posts
4
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

4. Registered User
Join Date
Oct 2003
Posts
1,091
I used that formula above with your guidelines and worked fine.

What does the cell show when you use the sumproduct?

5. Registered User
Join Date
Jan 2004
Location
Zambia
Posts
4
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

6. Registered User
Join Date
Oct 2003
Posts
1,091
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?

7. Registered User
Join Date
Jan 2004
Location
Zambia
Posts
4
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

8. Registered User
Join Date
Oct 2003
Posts
1,091
Great! Glad it is working for you!!!

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•