Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Array Formula: (Excel 97)

    I have played with this for ages and can't make it work so help please I want to count the entries in a range if the corresponding entry in another range has a value, in this case text/non blank. The spreadsheet attached demonstrates the principal, I'm sure this should be easy but somehow I can't get it. In terms of the spreadsheet what I want to do is say If "Type" entry has a entry then count the "Got It" colum if that has an entry
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula: (Excel 97)

    This does it:

    =SUM((B2:B6<>"")*(C2:C6<>"")*1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula: (Excel 97)

    Jan Karel forgot to mention that his formula must be array entered to work. Hold down the Shift+Ctrl keys when you press enter. Excel should insert {} around the formula if you do this correctly.
    Legare Coleman

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula: (Excel 97)

    Thanks for the addition, Legare.

    I guess it was because of the title of the question that I did not mention it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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