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

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

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

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

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