COUNTIF in Microsoft Excel?

Question

How would I go about using the function for two conditions?

For example, I have a set of data and I need to put it into a frequency table with frequency for numbers lower than 1, and another greater than 1 but less than or equal to 2... and greater than two AND less or equal to 3 etc.

Thanks in advance.

Answer 1

As I'm sure you realise, the COUNTIF function shows the number of items in a list.  If you also want to identify which items in your set of data match your criteria, you could use Conditional Formatting as follows (assuming your data is in cells A1:A100)

1) Highlight cells A1 to A100

2) Click on Format > Conditional Formatting

3) Click on "Cell Value Is" and change to "Formula Is"

4) In the next field enter the formula: =A1<1

5) Click on Format and select Font and / or Patterns tab and your preferred choice e.g. bold font or red pattern colour

6) Click OK

7) Click Add >>

8) Repeat step 3)

9) In the next field enter the formula: =AND(A1>=1,A1<=2)

10) Repeat steps 5) to 8) (choose a different format to Condition 1)

11) In the next field enter the formula: =AND(A1>=2,A1<=3)

12) Repeat steps 5) and 6) (choose a different format to Conditions 1 and 2)

13) Click OK

Hope this helps

Answer 2

Assuming data is in cells A1 to A100:

Bin 1 (numbers less than or equal to 1)

=COUNTIF(A1:A100,"<=1")

Bin 2 (numbers greater than 1 but less than or equal to 2)

=COUNTIF(A1:A100,"<=2") - COUNTIF(A1:A100,"<=1")

Bin 3 (numbers greater than 2 but less than or equal to 3)

=COUNTIF(A1:A100,"<=3") - COUNTIF(A1:A100,"<=2")

etc.

--

garfield-n-odie

Microsoft MVP 2005-2008

http://mvp.support.microsoft.com