Countif with multiple conditions

[Dartagnan]

Can I do a Countif with 2 or more conditions. For example I have a column of interest rates. I would like to count the number of rates that are greater than or equal to 5.0 but less than 6.0. Then how many are between 6.0 but less than 7.0 and so on til I am at the end.

Thank you

[Cam]

This would work for one of your conditions

Make sure to hit Ctrl+****+Enter after typing in the formula

{=COUNT(IF(AND(a1:a20>5,D6:D9<6),0))}

Hope that helps

[texasalynn]

try this:

=COUNTIF(C:C,"=0965")+COUNTIF(C:C,"=0960")

HTH
texasalynn

[texasalynn]

Did not know that was possible

Very good CAM

Thanks. . .texasalynn

[Dartagnan]

I know there are 43 rates that fall between 5.0 and 5.999. The formula only return a count of 1.

For reference all the interest rates are in column AA. I want to look through the column with the following criteria. If the rate is >= to 5.0 and <6.0 then give the count.

Thank you

[Aladin Akyurek]

On 2002-07-26 09:00, Dartagnan wrote:
Can I do a Countif with 2 or more conditions. For example I have a column of interest rates. I would like to count the number of rates that are greater than or equal to 5.0 but less than 6.0. Then how many are between 6.0 but less than 7.0 and so on til I am at the end.

Thank you

A between condition counting like you have can be done with COUNTIF:

Let A2:A100 house the interest rates, E1 the lower limit criterion/condition, e.g., 5.0, E2 the upper limit condition, e.g., 6.0:

=COUNTIF(A2:A100,">="&E1)-COUNTIF(A2:A100,">"&E2)

Aladin

[Dartagnan]

Sorry, but I am getting 0. Does it matter if the data is on another sheet?

[Dartagnan]

Think I found the answer. I changed from cell reference (E1) to "5.000" and the formula works.

Thank you so much!

[Henry]

But, what if you want the number to be a variable. That is, you don't want to put in a fixed number, instead, you want to make it a cell reference. So that all you have to do is put the new number in the cell instead of having to change the formula. How it won't accept cell references using count?

[Aladin Akyurek]

On 2002-07-26 12:47, Henry wrote:
But, what if you want the number to be a variable. That is, you don't want to put in a fixed number, instead, you want to make it a cell reference. So that all you have to do is put the new number in the cell instead of having to change the formula. How it won't accept cell references using count?

I thought Dartagnan was using the formula I proposed!