Results 1 to 8 of 8
  1. #1
    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. #2
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    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. #4
    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?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Jan 2004
    Location
    Zambia
    Posts
    4
    Originally posted by shades
    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. #6
    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?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Jan 2004
    Location
    Zambia
    Posts
    4
    Originally posted by shades
    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. #8
    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!
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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