Results 1 to 4 of 4
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    905
    Thanks
    154
    Thanked 11 Times in 10 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,330
    Thanks
    3
    Thanked 218 Times in 201 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, USA
    Posts
    905
    Thanks
    154
    Thanked 11 Times in 10 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
  •