Function to count numbers

[shabnam14]

Hello,

I need to count numbers in a column, which contains different values, example 0.00, 2.30, 1.02, 0.23 ,0.04 ,0.000042 and 2 blank cells
I am trying to use "Countifs" function to provide me the result and it is helping me by not counting "0" and blank cells, and provides me the result "5", however i want to include a condition where it doesn't count the cell which has value 0.000042 or similliar.

Hope the above explanation is clear and not confusing.

Thanks
Shab

 

[Joe4]

Welcome to the Board!

i want to include a condition where it doesn't count the cell which has value 0.000042 or similliar.

Please define what you mean by "or similiar".

 

[shabnam14]

Welcome to the Board!


Please define what you mean by "or similiar".


Hello,

Thank you for quick response.
I have a cell which has values likes, (0.000042, 0.000300,-0.000042)

Hope it helps.

 

[Joe4]

OK, so what is the exact criteria of values we want to exclude?
Is it based on decimal places?
Or values that are under a certain amount (like less than 0.01)?

 

[shabnam14]

OK, so what is the exact criteria of values we want to exclude? any cells which has values, 0, blank and values with more two decimal point, example (0.001234) so any number appearing in two decimal places (0.23 or 0.03) i want to count them.
Is it based on decimal places? Yes
Or values that are under a certain amount (like less than 0.01)? all the values are different

 

[Joe4]

Let's say that your values are in cells A1:A100.
Then try a formula like this:

=SUMPRODUCT(--(A1:A100<>0),--(A1:A100-ROUND(A1:A100,2)=0))

 

[shabnam14]

i tried this formula on the range below, however it gave me a count only 1 instead of 2.

[TABLE="width: 70"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]0.22[/TD]
[/TR]
[TR]
[TD="align: right"]2.336[/TD]
[/TR]
[TR]
[TD="align: right"]0.000123[/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]

Thanks
Shab[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

You said it was based on decimal places, so based on your original sample, I excluded everything with over two decimal places.
Your value of 2.336 has three decimal places, so would not be counted.

In order to come up with a rule that works for you, we need clearly defined rules.
You have a decision to make. How many decimal places do you want to allow?

Note in my formula you would just change the number in red to match the number of decimal places you wish to allow:

=SUMPRODUCT(--(A1:A100<>0),--(A1:A100-ROUND(A1:A100,[COLOR=#ff0000]2[/COLOR])=0))

 

[shabnam14]

You said it was based on decimal places, so based on your original sample, I excluded everything with over two decimal places.
Your value of 2.336 has three decimal places, so would not be counted.

In order to come up with a rule that works for you, we need clearly defined rules.
You have a decision to make. How many decimal places do you want to allow?

Note in my formula you would just change the number in red to match the number of decimal places you wish to allow:

=SUMPRODUCT(--(A1:A100<>0),--(A1:A100-ROUND(A1:A100,[COLOR=#ff0000]2[/COLOR])=0))

Hi,
Sorry about the confusion, i need it upto 2 decimal places.
I have applied the formula but it is giving me incorrect count.

Is there any way that i can email you spreadsheet.
It will provide you more clear picture.


Thanks

 

[Joe4]

Sorry about the confusion, i need it upto 2 decimal places.
I have applied the formula but it is giving me incorrect count.

That is exactly what my formula already does.

Look at your previous example:

i tried this formula on the range below, however it gave me a count only 1 instead of 2.

[TABLE="class: cms_table, width: 70"]
<tbody>[TR]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]0.22[/TD]
[/TR]
[TR]
[TD="align: right"]2.336[/TD]
[/TR]
[TR]
[TD="align: right"]0.000123[/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

Do you see that only one of those records meets that criteria? There is only one non-zero number that has 2 decimal places or less.

Also note that you need to be careful when it comes to checking decimal places. The formulas will check their ACTUAL values, not necessarily the DISPLAYED values.
For example, if you enter this in any cell:
=1/3
it will show you:
0.333333...

Now, if you format the cell to show two decimal places, it will return:
0.33
However, the REAL value has more decimal places, and the formula will look at that, not the displayed value.

If you remove all formatting and just set the Format to "General", you will see the real value the cell hold.
So be aware of this with your formulas.

If you wanted the real value in the cell to only hold two decimal places, you would need to use the ROUND function, i.e.
=ROUND(1/3,2)
or change the "Precision as Displayed" setting.