Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count array formula (xp)

    Please check the formula in J6.
    Column G is a count of how many drugs each patient is on and column H is which drugs they are on.
    What I am trying to find out is the count of people who are on one drug and that one drug is insulin.

    Thanks much

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: count array formula (xp)

    Try

    <code>=SUMPRODUCT(-(E2:E100=1),-(F2:F100="INSUL"))</code>

    This is a "normal" formula, not an array formula. The minuses are to force the result to be numeric. Another possibility is the array formula

    <code>=SUM((E2:E100=1)*(F2:F100="INSUL"))</code>

    (confirm with Ctrl+Shift+Enter)

  3. #3
    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

    Re: count array formula (xp)

    I think you meant:

    "Please check the formula in <font color=red>H</font color=red>6.
    Column <font color=red>E</font color=red> is a count of how many drugs each patient is on and column <font color=red>F</font color=red> is which drugs they are on.
    What I am trying to find out is the count of people who are on one drug and that one drug is insulin."

    Try this:
    <pre>=SUMPRODUCT((E2:E100=1)*(F2:F100="insul"),E2: E100)</pre>


    Steve

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count array formula (xp)

    Right you are Steve.

    They all worked - thanks all!

Posting Permissions

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