Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Counting Multiple Criteria (2000)

    I have a table of data results, thus:

    <table border=1><td>A</td><td>B</td>
    <td>1</td><td>1</td><td>0</td><td>1</td><td>1</td><td>0</td>
    </tr><td>1</td><td>0</td>

    </table>

    I have been requested to count where there is a 1 column A and a 0 in column B and vice versa. I have used

    {=SUM((A2:A1624=0)*(B2:B1624=1))} and

    {=SUM((A2:A1624=1)*(B2:B1624=0))} two seperate cells to get the two seperate counts but get the same answer, have I missed something here <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> or am I using the wrong method?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting Multiple Criteria (2000)

    Sounds OK - perhaps the numbers ARE the same?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting Multiple Criteria (2000)

    Thanks Hans

    I really thought it was me, I have approximately 40000 records and by coincidence the count is exactly the same for the different criteria. I have just made a change to the data and it does change.

    <img src=/S/note.gif border=0 alt=note width=20 height=20> to myself...I must not doubt yourself, I am a Tiger Grrrrrr
    Jerry

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Multiple Criteria (2000)

    You may have 40000 records, but your formula are only counting 1623 of them.
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting Multiple Criteria (2000)

    Thanks Legare, well spotted.....this was a sample set of 1624 records ( a little testette before I went onto the monster), the 40000 odd were on sheet2. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Counting Multiple Criteria (2000)

    <hr>I must not doubt yourself<hr>
    having a schizophrenic moment were we? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting Multiple Criteria (2000)

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Yes we were, but I very happy with him now.
    Jerry

Posting Permissions

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