98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » excel-xp-training - Countif function
excel-xp-training - Countif function
Resolved · Low Priority · Version Standard
Ben has attended:
Excel VBA Intro Intermediate course
Access Introduction course
Countif function
I need to runa countif function with some strict criteria, can someone please give me a formula that will do the following:
Count if any entry in a range is greater than one number and less than another.
Many thanks,
Ben
RE: Countif function
Hi Ben
Thank you for your question.
Using the DCOUNT function may help solve your problem.
I've attached an example so you can see how this works. The formula is in the yellow cell.
Let me know if you have any further questions.
Amanda
RE: Countif function
Amanda,
Thank you for your reply. Unfortunately this has not worked probably due to the followig reasons:
The data in my case is horizontal which gives a #VALUE response.
Thanks,
Ben
RE: Countif function
Hi Ben
Yes, unfortuately this will only work on data arranged in columns.
Would it be possible to transpose the data? Copy it, then use Edit - Paste Special, tick Transpose and OK.
Amanda
RE: Countif function
I'd go about it a different way.
in a column you won't be using, e.g. column CC, type = if(AND(A1>5,A1<10),1,0)
Then you can do a sum of that column.
If you have a range that won't be the same, try this: (Untested)
=if(isna(and(A1>5,A1<10),1,0),"",and(A1>5,A1<10),1,0)
this will return a blank instead of #N/A, allowing you to write your sum formula as =sum(C:C) rather than having to change the formula to it being the last cell in column C with data in.
Hope I am making sense?
Paul
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Finding cells that have data restrictionsClick anywhere on the worksheet. |