Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel-xp-training - Countif function
|
|
|
The UK's most regular 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-xp-training - Countif function
Resolved · Low Priority · Version Standard
Reply Mon 7 Jan 2008, 11:01Delegate Ben said...
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
For upcoming training course dates see: Pricing & availability
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
Reply Mon 7 Jan 2008, 15:29Delegate Ben said...
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
Reply Mon 7 Jan 2008, 16:54Delegate Paul said...
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
|