Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Seattle, Washington, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count if 2 text values are true (Excel 2002)

    I need to compare 2 text string values in an array and based on if they both are true, count 1.

    For example:
    A1:B10 = array
    .
    .
    .
    A4 = "Closed" B4 = "String1"
    A5 = "Open" B5 = "String1"
    A6 = "Open" B6 = "String2"
    .
    .
    .

    What I need:
    LOOP THROUGH A1:A10 ( IF A* = "Open" AND B* = "String1", THEN Count 1 @ C11)

    - loops through A1:A10, finds A5 = True, then compares adjacent value B5... if B5 also is True, then count 1 at C11.


    Can anyone help?

    Thank you,
    Aaron

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

    Re: Count if 2 text values are true (Excel 2002)

    The following formula, entered as an array formula (i.e. confirm with Ctrl+Shift+Enter) will do this:

    =SUM((A1:A10="Open")*(B1:B10="String1"))

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Seattle, Washington, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if 2 text values are true (Excel 2002)

    Thanks for the post Hans. That was exactly what I needed.

    I have another question while I'm at it. Say I have a total of 3 strings (string1, string2 and string3) in column B. Column A is still only Open and Closed strings. I need string1 and string3 to be grouped for results, but keep string2 separate. I tried modifying the formula listed above, but got varied results, none of which I was looking for.

    So... IF A1:A10="Open" AND B1:B10="string1" OR "string3" THEN count 1.

    Any takers?

    Take care,
    Aaron

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

    Re: Count if 2 text values are true (Excel 2002)

    Try this array formula:

    <pre>=SUM((A1:A10="Open")*((B1:B10="String1")+(B1: B10="String3")))
    </pre>

    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Feb 2004
    Location
    Seattle, Washington, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if 2 text values are true (Excel 2002)

    Thank you sir... a '+' operator is the only thing I didn't try. [img]/forums/images/smilies/smile.gif[/img]

    - Aaron

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if 2 text values are true (Excel 2002)

    Would these also work?

    Formulas are entered "regularly" not with CSE

    =SUMPRODUCT(--(A1:A10=G1),--(B1:B10=E1))

    =SUMPRODUCT(--(A1:A10=G1),--ISNUMBER(MATCH(B1:B10,E1:F1,0)))

    Where G1 houses Open
    E1 houses String 1
    F1 houese String 3

Posting Permissions

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