How do I do the following? :


A B C


1 Q1 Yes
2 Q2 Yes
3 Q3 No
4 Q1 Yes
5 Q1 Yes

How can I code, Count the no. of times cell A1:A5 have "Q1" in it with
"Yes" in the respective "C" cell?
(In this eg. Q1 and Yes exist together 3 times)

=sumproduct(--(a1:a5="Q1"),--(c1:c5="yes"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

wrote:

How do I do the following? :

A B C

1 Q1 Yes
2 Q2 Yes
3 Q3 No
4 Q1 Yes
5 Q1 Yes

How can I code, Count the no. of times cell A1:A5 have "Q1" in it with
"Yes" in the respective "C" cell?
(In this eg. Q1 and Yes exist together 3 times)

--

Dave Peterson

Great ! This works! Thank you for your help!

On Jan 27, 7:08 pm, Dave Peterson wrote:
=sumproduct(--(a1:a5="Q1"),--(c1:c5="yes"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail hehttp://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html

wrote:

How do I do the following? :

A B C

1 Q1 Yes
2 Q2 Yes
3 Q3 No
4 Q1 Yes
5 Q1 Yes

How can I code, Count the no. of times cell A1:A5 have "Q1" in it with
"Yes" in the respective "C" cell?
(In this eg. Q1 and Yes exist together 3 times)--

Dave Peterson