How do I count the number of cells in column A that contain a certain string
but only if the same row in column B contains a certain string

eg

A B
Elective Yes
Elective No
Urgent Yes
Urgent No
Elective Yes

How do I count the number of rows that contain Elective in column A and Yes
in column B. The answer here would be 2.

Try this:

=Sumproduct((A1:A5="Elective")*(B1:B5="Yes"))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"JRD" wrote in message
...
How do I count the number of cells in column A that contain a certain string
but only if the same row in column B contains a certain string

eg

A B
Elective Yes
Elective No
Urgent Yes
Urgent No
Elective Yes

How do I count the number of rows that contain Elective in column A and Yes
in column B. The answer here would be 2.

=sumproduct((a2:a22="Elective")*(b2:b22="yes"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JRD" wrote in message
...
How do I count the number of cells in column A that contain a certain
string
but only if the same row in column B contains a certain string

eg

A B
Elective Yes
Elective No
Urgent Yes
Urgent No
Elective Yes

How do I count the number of rows that contain Elective in column A and
Yes
in column B. The answer here would be 2.

Try this:

=SUMPRODUCT(--(A1:A5="Elective"),--(B1:B5="Yes"))

Better to use cells to hold the criteria.

D1 = Elective
E1 = Yes

=SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1))

--
Biff
Microsoft Excel MVP


"JRD" wrote in message
...
How do I count the number of cells in column A that contain a certain
string
but only if the same row in column B contains a certain string

eg

A B
Elective Yes
Elective No
Urgent Yes
Urgent No
Elective Yes

How do I count the number of rows that contain Elective in column A and
Yes
in column B. The answer here would be 2.

Thanks

Can you tell me what -- does before the paratheses?

John

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Elective"),--(B1:B5="Yes"))

Better to use cells to hold the criteria.

D1 = Elective
E1 = Yes

=SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1))

--
Biff
Microsoft Excel MVP


"JRD" wrote in message
...
How do I count the number of cells in column A that contain a certain
string
but only if the same row in column B contains a certain string

eg

A B
Elective Yes
Elective No
Urgent Yes
Urgent No
Elective Yes

How do I count the number of rows that contain Elective in column A and
Yes
in column B. The answer here would be 2.

Sumproduct works with logicals (True, False), so the double unary ( -- )
converts them to numbers (True=1, False=0) so the returns can be calculated.

Check out these web sites for further explanations:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you'll notice, my suggested formula uses the multiplication operator (
* ) to accomplish the same result.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JRD" wrote in message
...
Thanks

Can you tell me what -- does before the paratheses?

John

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Elective"),--(B1:B5="Yes"))

Better to use cells to hold the criteria.

D1 = Elective
E1 = Yes

=SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1))

--
Biff
Microsoft Excel MVP


"JRD" wrote in message
...
How do I count the number of cells in column A that contain a certain
string
but only if the same row in column B contains a certain string

eg

A B
Elective Yes
Elective No
Urgent Yes
Urgent No
Elective Yes

How do I count the number of rows that contain Elective in column A
and
Yes
in column B. The answer here would be 2.