How to use count with multiple conditions

  • Thread starter Thread starter Ming
  • Start date Start date
M

Ming

I have a table in Excel:
The first row is time in years.
The second row is method name,say,"A","B","C".

I want to count the number when the time is less than 5 years AND "A"
method is adopted.

I tried this:
count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.

Any suggestion on how to revise the formula?

Thanks!

In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
countif(C2:Z2,"<5")

Ming
 
=SUMPRODUCT(--(C2:Z2<5),--(C3:Z3="A"))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Or enter the following as an array:
count(if(C2:Z2<5,if(C3:Z3="A"­,C2:Z2))

To enter it as an array hit ctrl-shift-enter, rather than just the
<enter> key.

Jim Shoenfelt
 
Hi,Bob

what's the meaning of the operator "--" in your formula? To transfer
logic value into 1/0?

Thanks!
Ming
 
Exactly that.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Hi! Guys, I also wonder how to use COUNT when I have to count the
absolute values. Say I have M1:M100 and I want to count that how mang
the values within this range whose absolute values are smaller than 1.
I tried COUNTIF(ABS(M1:M100), "<1) or COUNT(IF(ABS(M1:M100),<1,
M1:M100). Both didn't work. Any suggestions to make it right? Thank
you in advance!
 
=SUMPRODUCT(--(M1:M100<>""),--(ABS(M1:M100)<1))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top