Results 1 to 6 of 6

20040225, 21:23 #1
 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

20040225, 21:34 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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"))

20040226, 01:08 #3
 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

20040226, 03:25 #4
 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

20040226, 16:51 #5
 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

20040229, 21:46 #6
 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