Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count conditions (2003)

    Hi,
    Can a count be done based upon 2 conditions? eg:
    COUNT IF A:A="X" and IF B:B="Y"
    Got my stoopid brain in tonight!!
    Thanks
    Nathan

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

    Re: Count conditions (2003)

    You can use SUMPRODUCT for this, but you have to specify a "finite" range instead of entire columns. For example:

    =SUMPRODUCT(($A1:$A10000="X")*($B1:$B10000="Y"))

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count conditions (2003)

    Perfect! Thanks Hans, that is not a function that I have ever used before.

    Nathan

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count conditions (2003)

    Is there a similar function for sum? Only I can't get this to work.
    SUMIF(AND(DEBITS!A3:A65536,"XXX"),(DEBITS!P3:P6553 6,"XXX")),DEBITS!F3:F65536)
    Thanks

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

    Re: Count conditions (2003)

    You have to use SUMPRODUCT again:
    <code>
    =SUMPRODUCT((DEBITS!A3:A65536="XXX")*(DEBITS!P3:P6 5536="XXX")*DEBITS!F3:F65536)
    </code>
    Note: Excel 2007 introduces new functions COUNTIFS, SUMIFS and AVERAGEIFS that let you specify multiple conditions. In Excel 2003 and before, you have to use SUMPRODUCT (or a combination of SUM and IF in an array formula)

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count conditions (2003)

    Thanks Hans, perfect again!

    I'm using Excel 2003. I can use SUMIF and COUNTIF for single conditions but not multiple conditions? It may be an add in that I have?

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

    Re: Count conditions (2003)

    SUMIF and COUNTIF only let you specify a single condition. If you search, you'll find numerous threads here and in other discussion forums about how to calculate sums and counts with multiple conditions. In Excel 2007, Microsoft finally added new functions.

    I obviously don't know which add-ins you have installed...

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count conditions (2003)

    Sorry Hans, After re-reading your reply, I have noticed that you were referring to sumifS, countifS, which I now see are different to plain sumif and countif. I had'nt realised. Thanks again.

Posting Permissions

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