+ Reply to Thread
Results 1 to 6 of 6

Count Number of times Column A says "X" where Column B says "Y"

  1. #1
    Registered User
    Join Date
    09-10-2012
    Location
    Sheffield
    MS-Off Ver
    Excel 2007
    Posts
    17

    Count Number of times Column A says "X" where Column B says "Y"

    I'm just wondering how a formula would work where I am wanting Column A says "X" where the same rows in Column B says "Y"?

    This is for 500 datasets and I require the answer to be displayed in another column, which appears on a different worksheet, not sure if this affects the formula much.

    Thanks

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Count Number of times Column A says "X" where Column B says "Y"

    =countifs(a1:a500,"=x",b1:b50,"=y") should do it
    mind you just
    =countifs(a1:a500,"x",b1:b50,"y") should work
    Last edited by martindwilson; 09-10-2012 at 06:17 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Count Number of times Column A says "X" where Column B says "Y"

    Deleted post.
    Last edited by Kevin UK; 09-10-2012 at 06:14 AM.

  4. #4
    Registered User
    Join Date
    08-17-2012
    Location
    Lisbon, Portugal
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    82

    Re: Count Number of times Column A says "X" where Column B says "Y"

    Hello,

    My first attempt would be trying to do a COUNTIF formula combining those two variables.
    If that first attempt would fail, do 2 separate countif's and add them in one unic formula using the "+"(add) between them. Such as:

    COUNTIF(A:A;"X") + COUNTIF(B:B;"Y")


    If you're not sucessfull provide a file so I can have a look.


    EDIT: I was too late



    Kind Regards,
    FCarv

  5. #5
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Count Number of times Column A says "X" where Column B says "Y"

    Try

    =sumproduct(--(a:a="x")*--(b:b="y"))
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  6. #6
    Registered User
    Join Date
    09-10-2012
    Location
    Sheffield
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Count Number of times Column A says "X" where Column B says "Y"

    Thanks all for the quick replies.

    Countifs worked perfect.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1