Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an excel sheet with 2 columns of data - all text rather than numeric. The first column contains 4 different text values: A, IO PO and S. The second column either contains a Y or a blank. I would like to count the number of Ys in the second column against the 4 categories in the first column - ie for those recorded as A in the 1st column, how many of these recorded Y in the second column?

    Should I be using an array formula, or some version of COUNTIF?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    If you're using Excel 2007, you can use the new COUNTIFS function. For example, if your data are in A2:B100:

    =COUNTIFS(A2:A100,"A",B2:B100,"Y")

    In Excel 2003 and before, you have to use a different method. For example, if your data are in A2:B100:

    =SUMPRODUCT((A2:A100="A")*(B2:B100="Y")

    This is a "normal" formula, not an array formula (the array aspect is implicit in SUMPRODUCT).

Posting Permissions

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