Results 1 to 4 of 4
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Need a COUNTIF formula

    This is for a sheet in the fairly intricate Lottery Workbook that many have helped with so far. Like many things in this workbook, it's probably easy, but I haven't been able to figure it out. As often happens, just framing this question has gotten me closer to the solution:

    The cells in unnamed range N6:N9999 contain either "yes" or "no", or are empty. If I named this range, it would be ParticipantIn.

    The cells in unnamed range O6:O9999 also contain either "yes" or "no", or are empty. If I named this range, it would be ParticipantRankin.

    In cell R16 I need to put a count of the rows where the cells in both Column N and Column O contain a "yes".

    How can I do it?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =SUMPRODUCT((ParticipantIn="yes")*(ParticipantRank in="yes"))

    or with they are not named:
    =SUMPRODUCT((N6:N9999="yes")*(O6:O9999="yes"))

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Lou Sander (2012-05-26)

  4. #3
    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
    or for 2007+

    =COUNTIFS(ParticipantIn,"yes",ParticipantRankIn,"y es")

    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    Lou Sander (2012-05-26)

  6. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    SDC's works great! I'm still using Excel 2003, but will soon upgrade. Then I'll try RORY's.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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