Tom’s Tutorials For Excel: Count Percentages in a Range
You can count how many percentage numbers fall within an upper and lower range. In the picture, a pair of lower and upper range percent values in cells D2 and E2 serve as criteria for your count.
The formula in cell D5 that refers to those cells is =SUMPRODUCT((B2:B11>=D2)*(B2:B11<=E2)).
Couldn’t you do the same thing with COUNTIFS?
=COUNTIFS(A1:A13,”>”&0.9,A1:A13,”<"&0.95)
Thanks for following my Excel blog page Dave.
I’m sure this formula could be done other ways, including COUNTIFS as you suggested. I get similar comments on my Twitter page where I post half a dozen such tweets daily (link is on this page) and elsewhere on my site here). My approach with every Excel tip and example I post is, unless I specify otherwise that it’s only applicable to a particular version (such as PowerPivot for free only in 2010), or will work starting with a particular version (such as COUNTIFS with or after 2007), I make my formulas and code fit every version, as a one size fits all. There are still between 20%-25% of Excel users world wide still working with version 2003. For them, which is almost one-fourth of the Excel user population, COUNTIFS would force an error and the tip would be useless. That’s how I go about my sharing of tips and tutorials, in that even though later versions can use a newer function, I want all version users to benefit.
This does not work!!! Excel will not accept the comparison symbols of > < = etc. without their being enclosed in quotes, which it then rejects. I have tried this on COUNTS and SUMPRODUCT and neither formula works. So frustrating. All I want to do is count the number of cells that contain a percentage figure in a row, while excluding those that contain a currency amount. All the assistance I have tried using tips from the net have failed. I am reluctant to use a VBA script as I don't want the workbook to be an xlsx format.
Yes it does work, as you can see in the picture that is shown, with the formula evident in the formula bar. Something else is going on with your workbook or your data that is causing the non-result you are seeing. Check for extra space characters in your formula, and check your data being referenced in case it is formatted as text and not as number. Just a couple initial thoughts – – something must be going on to cause the effects you are seeing.