Hi,
I'm looking for some guidance on a formula that will count the number of times a value from 1-99 occurs over a range of cells...
The number of times (occurences) the value happens is what's important...
In example.
The range is d1:d50
Within this range, I'm looking for a 'count' of how many times any value between 1-99 occurs at leastTHREE times.
Doesn't matter what the value is, it's the count I'm interested in.
Thank you,
chubs
maybe something like...
=if(countif($D$1:$D$50,D1)>2,countif($D$1:$D$50,D1))
or
=if(countif($D$1:$D$50,D1)>2,"some text here")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you for the quick response.
The formula works fine on col. d.
When I remove "$" and try on other columns it comes up with a FALSE on columns that have 3 or more occurences.
Trying to figure out why.
Here's the formula: =IF(COUNTIF(AR49:AR78,AR49)>2,COUNTIF(AR49:AR78,AR49)).
Rgds,
Chubs
If you are wanting the number of values that are between 0 and 99 inclusive that occur 3 or more times, this is how I tackled the problem:
Use column E as a helper column and enter this formula to count only the numbers that are under 100
Formula:
Please Login or Register to view this content.
Then use this formula:
Formula:Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
put the absolute ($) back
Does this do what you want:
=SUM(--(FREQUENCY(D1:D50,D1:D50)>=3))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony,
=SUM(--(FREQUENCY(D1:D50,D1:D50)>=3))
works great! Perfect solution.
Thank you,
Chubs
Thanks to everyone who responded to my query as well.
What if the values in the range are over 99 or less than 1 as per your problem definition?..."Within this range, I'm looking for a 'count' of how many times any value between 1-99 occurs at leastTHREE times."
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks