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

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

5. ## 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. ## 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
•