CountIf - Multiple Criteria

[Lintz]

I'm trying to use CountIf to count how many times in the below exampl
Up occurs in Column A and Low occurs in column B. With the belo
example it should return 2.

Can someone help me out.

Thanks.

A B
1 Up Low
2 Up Low
3 Up High
4 Down Hig

 

[Chip Pearson]

Use a formula like the following:

=SUMPRODUCT((A1:A4="Up")*(B1:B4="Low"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(A1:A10="Up"),--(B1:B10="Low"))

 

[Chip Pearson]

It has become de rigueur to use the "--" syntax for SUMPRODUCT in
this context, but just for the record, a single unary negation
will suffice. And for those counting clock cycles, it is slightly
faster.

=SUMPRODUCT(-(A1:A10="Up"),-(B1:B10="Low"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Hi
try
=SUMPRODUCT(--(A1:A10="Up"),--(B1:B10="Low"))

--
Regards
Frank Kabel
Frankfurt, Germany

I'm trying to use CountIf to count how many times in the

below

 

[Dave Peterson]

I think that the fear is someone will use that as a template formula and add a
few terms. And they won't notice if the number of terms is odd or even.

Then they may end up with a negative count <bg>.

It has become de rigueur to use the "--" syntax for SUMPRODUCT in
this context, but just for the record, a single unary negation
will suffice. And for those counting clock cycles, it is slightly
faster.

=SUMPRODUCT(-(A1:A10="Up"),-(B1:B10="Low"))

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Lintz]

Thanks - It works perfectly :

 

[sowetoddid]

What does this sumproduct formula not work for the following

3 Yes
4 No
5 Yes
3 No


It should sum all row showing both "3" and "Yes"


Thanks

 

[JE McGimpsey]

Do you mean SUM or count?

to count all rows showing both 3 and Yes:

=SUMPRODUCT(--(A1:A4=3),--(B1:B4="Yes"))

to sum:

=SUMPRODUCT(--(A1:A4=3),(B1:B4="Yes"),A1:A4)

or

=SUMPRODUCT(--(A1:A4=3),--(B1:B4="Yes")) * 3

This assumes that the values in A1:A4 are numbers, not Text. If they're
Text, try:

=SUMPRODUCT(--(A1:A4="3"),--(B1:B4="Yes"))