Countif Question

  • Thread starter Thread starter b4nature
  • Start date Start date
B

b4nature

How do I get a formula to automatically update to count the number of times a
changing value exists within the same range of cells?

Example:
If my range needs to always be B2:F350, how can I count within that range
how many times the number 1 appears, then 2, then 3, etc.

I have COUNTIF($B$2:$F$350,"=1") but I don't want to have to change the
number every time. I want to copy down so the 1 automatically changes to 2
then 3...

Thanks!
 
b4nature said:
How do I get a formula to automatically update to count the number of times a
changing value exists within the same range of cells?

Example:
If my range needs to always be B2:F350, how can I count within that range
how many times the number 1 appears, then 2, then 3, etc.

I have COUNTIF($B$2:$F$350,"=1") but I don't want to have to change the
number every time. I want to copy down so the 1 automatically changes to 2
then 3...

Thanks!
Click to expand...

Try this:

=COUNTIF($B$2:$F$350,ROW(1:1))
 
In the very first row in some column enter:

=COUNTIF($B$2:$F$350,ROW()) and copy down
 
In what cells do you want the results to appear?

Let's assume you want the results starting in cell H2.

Enter this formula in H2 and copy down as needed:

=COUNTIF(B$2:F$350,ROWS(H$2:H2))

But, how will you know what that result is for? The first few will be
obvious:

5
7
6
10
12

Why don't you identify the category like this:

1...5
2...7
3...6
4...10
5...12

This is easy to do.

Let's put the category in column H and the counts in column I.

Enter this formula in H2:

=ROWS(H$2:H2)

Enter this formula in I2:

=COUNTIF(B$2:F$350,H2)

Select both H2 and I2 and drag copy down as needed.
 
Back
Top