How to use count with multiple conditions

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)


"Ming" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Or enter the following as an array:
count(if(C2:Z2<5,if(C3:Z3=3D"A"=AD,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

It works!
Thanks, Jim

Exactly that.

--

HTH

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


"Ming" <[email protected]> wrote in message
news:[email protected]...
> Hi,Bob
>
> what's the meaning of the operator "--" in your formula? To transfer
> logic value into 1/0?
>
> Thanks!
> Ming
>

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)


"hong" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

Thanks! Bob, it works perfect.

I am trying to do something similar - use count (and later, sum) with multiple conditions, but with slightly more complicated criteria.

On a simple table with only 10 rows, a pair of cells containing the data to match against, and the formula all on the same sheet, I can get this formula to work:

Please Login or Register  to view this content.

However, for my real scenario, I have the data to be counted on one sheet (called "Answered"), the data to match against and the formula on a second sheet... and the matching has to be partial - ie, using wildcards. My first criteria (in cell B10) is a 4-digit number which is contained at the beginning of a text string in the target range (column B). My second criteria (in cell A10) is a 5-digit number contined somewhere within a text string in the target range (column C).

I have used mixed reference type because the same formula will be applied to different pairs of criteria as you go down rows in a table.

If I match on only one criteria, this formula will work:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

However, I need to match against two - I need the rows where both conditions are true. I have tried all of the following, and they give me a result of 0.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This gives a result of 1:

Please Login or Register  to view this content.

What am I doing wrong?

Just fyi - counting the rows where both conditions are true is only the beginning of my woes - later I will have to sum, max, or average date/time data from a third column in the rows where both of the conditions are true.

thanks it worked