CountIf - Multiple Criteria

L

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
 
C

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
 
C

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



Frank Kabel said:
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
Click to expand...
below
Click to expand...
 
D

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

Chip said:
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
Click to expand...
 
S

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
 
J

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"))
 
Top