# Countif Decimal is within a range

1. ## Countif Decimal is within a range

Hello,

I have a list of almost 200 numbers, all of them to the nearest hundredth point.
What I am trying to accomplish is to do a CountIf(Decimal points is between .98 and .02) regardless of what the whole number is.
I keep getting and answer of 0 ``Please Login or Register  to view this content.``
Example
5.00
6.45
7.86
3.98
2.99
4.01
12.02
8.24

Should = 5

Any Assistance would be appreciated!  Register To Reply

2. ## Re: Countif Decimal is within a range

first stab which isnt elegant
=SUMPRODUCT((MOD(A1:A8*100,100)<=2)*(MOD(A1:A8*100,100)>=0))+SUMPRODUCT((MOD(A1:A8*100,100)>=98)*1)

or perhaps

=SUMPRODUCT((MOD(A1:A8*100-98,100)<=4)*1)  Register To Reply

3. ## Re: Countif Decimal is within a range

Please try
=SUMPRODUCT((MOD(E18:E200-0.02,1)<=0.96)*(E18:E200<>""))  Register To Reply

4. ## Re: Countif Decimal is within a range

Davsth and Bo_Ry,

Thanks, those were close but it's still not hitting the proper end value. I attached an Example File for reference.
Thank you for the help!  Register To Reply

5. ## Re: Countif Decimal is within a range

You've got rounding error (i.e. displayed number isn't what's stored, should be rounded to nearest 2nd decimal). Something like...
=SUMPRODUCT((ROUND(B3:B367,2)<=0.02)+(ROUND(B3:B367,2)>=0.98))

Or change B column formula to...
=Round(A3/\$A\$2,2)

And use simple COUNTIF with addition.
COUNTIF(B3:B367,"<=0.02")+COUNTIF(B3:B367,">=0.98")  Register To Reply

6. ## Re: Countif Decimal is within a range

=sumproduct(--(abs(round(b3:b367, 2) - round(b3:b367, 0)) <= 0.02))  Register To Reply

7. ## Re: Countif Decimal is within a range

shg,

That worked on my example file without issue! When I added it into my actual document I am getting a "#value" error.

=SUMPRODUCT(--(ABS(ROUND(E18:E200, 2) - ROUND(E18:E200, 0)) <= 0.02))

Any Ideas Why?

Thank you again!  Register To Reply

8. ## Re: Countif Decimal is within a range

Maybe your numbers are text masquerading as numbers?  Register To Reply

9. ## Re: Countif Decimal is within a range

No idea why.  Register To Reply

10. ## Re: Countif Decimal is within a range

They are set as Numbers in the Cells. I do have them in the Cell via a VLOOKUP function but even the source Cells are set as numbers.  Register To Reply

11. ## Re: Countif Decimal is within a range

I've Uploaded a new Example File  Register To Reply

12. ## Re: Countif Decimal is within a range

Please try
=SUMPRODUCT(--(MOD(ROUND(B3:B367+0.02,2),1)<=0.04))  Register To Reply

13. ## Re: Countif Decimal is within a range

It's floating point issue.

Though @Bo_Ry's formula is more efficient, below would work as well.
=SUMPRODUCT((ROUND(E3:E367-TRUNC(E3:E367),2)<=0.02)+(ROUND(E3:E367-TRUNC(E3:E367),2)>=0.98))  Register To Reply

14. ## Re: Countif Decimal is within a range I keep getting the #VALUE

Capture3.PNG  Register To Reply

15. ## Re: Countif Decimal is within a range

Bryan

Can you upload a sample file with the formulas that are giving you the error?  Register To Reply

16. ## Re: Countif Decimal is within a range

Norie, I can't attach my file but I did display the formulas used just above each #value error result in the picture above.  Register To Reply

17. ## Re: Countif Decimal is within a range

Yes, you can attach your file.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.  Register To Reply

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Tags for this Thread #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1