Using COUNTIF on cells with formulas

Topic

New Reply

I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,”>0″)+countif(value(range,”<2")))). I have something just not right because Excel doesn't like the formula. What am I not seeing?

TIA

Reply | Quote

Replies

I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,”>0″)+countif(value(range,”<2")))). I have something just not right because Excel doesn't like the formula. What am I not seeing?

TIA

Reply | Quote

I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,”>0″)+countif(value(range,”<2")))). I have something just not right because Excel doesn't like the formula. What am I not seeing?

TIA

Reply | Quote

I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,”>0″)+countif(value(range,”<2")))). I have something just not right because Excel doesn't like the formula. What am I not seeing?

TIA

Reply | Quote

I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,”>0″)+countif(value(range,”<2")))). I have something just not right because Excel doesn't like the formula. What am I not seeing?

TIA

Reply | Quote

I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,”>0″)+countif(value(range,”<2")))). I have something just not right because Excel doesn't like the formula. What am I not seeing?

TIA

Reply | Quote

I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,”>0″)+countif(value(range,”<2")))). I have something just not right because Excel doesn't like the formula. What am I not seeing?

TIA

Reply | Quote

I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,”>0″)+countif(value(range,”<2")))). I have something just not right because Excel doesn't like the formula. What am I not seeing?

TIA

Reply | Quote

How about this:
=COUNTIF(range,”>0″)-COUNTIF(range,”>=2″)

Steve

Reply | Quote

Steve, the formula works fine for counting all the 1s but now I need to count all the 2s, then the 3s, 4s, and 5s. I had to modify the formula because of rounding so the working formula is
=COUNTIF(K5:K161,”>0″)-COUNTIF(K5.K161,”>1.4″)
Do I need to use AND with the COUNTIF in order to just get the 2s?

TIA

Reply | Quote

How about this:
=COUNTIF(range,”>0″)-COUNTIF(range,”>=2″)

Steve

Reply | Quote

How about this:
=COUNTIF(range,”>0″)-COUNTIF(range,”>=2″)

Steve

Reply | Quote

How about this:
=COUNTIF(range,”>0″)-COUNTIF(range,”>=2″)

Steve

Reply | Quote

How about this:
=COUNTIF(range,”>0″)-COUNTIF(range,”>=2″)

Steve

Reply | Quote

How about this:
=COUNTIF(range,”>0″)-COUNTIF(range,”>=2″)

Steve

Reply | Quote

How about this:
=COUNTIF(range,”>0″)-COUNTIF(range,”>=2″)

Steve

Reply | Quote

How about this:
=COUNTIF(range,”>0″)-COUNTIF(range,”>=2″)

Steve

Reply | Quote

That did it. Thanks Steve.

Reply | Quote

That did it. Thanks Steve.

Reply | Quote

That did it. Thanks Steve.

Reply | Quote

That did it. Thanks Steve.

Reply | Quote

That did it. Thanks Steve.

Reply | Quote

That did it. Thanks Steve.

Reply | Quote

That did it. Thanks Steve.

Reply | Quote

That did it. Thanks Steve.

Reply | Quote

Making just a slight change to Steve’s formula…

=COUNTIF(range,”>=2″)-COUNTIF(range,”>=3″)

Should return the number of values that are greater than or equal to 2, while being less than 3. Is that what you needed?

Reply | Quote

If you want to count the rounded values, you could try:
=SUMPRODUCT(–(ROUND(K5:K161,0)=2))

Reply | Quote

Rory and Ricky, both formulas work but the one using ROUND seems easier since I can just use whole numbers. Thank you both very much.

Reply | Quote