Is it possible to code Excel COUNTIF with multiple criteria using variables?

Question

Consider this formula used in an Excel worksheet:

=COUNTIF(D133:D164,{"<140",">100"})

It gives me exactly the information I need from a table of blood pressure readings. The only thing wrong with it is that the criteria values are hard-coded.

Is it possible to code this so using variables that will be defined elsewhere on the sheet? The use of the brackets seems to be the obstacle.

I know there are other methods to get the same info, but if it is possible to use this one I'd like to know how to do it.

Thanks.

Answer 1

Hi Tom

Does this formula work for you?

=COUNTIFS(D133:D164,"<"&A1, D133:D164,,">"&A2)

Answer 2

Dave,

No joy yet I'm afraid. The statement can be entered without throwing an error, but it returns a 0, which should not be. I stepped through the Evaluate Formula and confirmed that A1 and A2 were resolving correctly, but then I hit a #VALUE error. Still looking at that. There may be another piece but I think we are close.

Answer 3

Hi Tom

Sorry, that is my fault, here is the working version . .

=COUNTIF(D133:D164,(AND("<"&A1,">"&A2)))

Answer 4

I tried making the change as per your suggestion and it threw an error. ("There is a problem with this formula.")

So I next tried copying the statement directly from your response to eliminate the possibility of my making a typo. I made sure there were values in A1 and A2. Got the same error. 

I got the same response last night with a similar concatenation, but using Defined Names. It just doesn't like those brackets!

If you can enter that statement without getting a syntax error I'm not sure what the problem could be.

Answer 5

Hi Tom,

Yes, you can easily do that if you use a formula like this, say of those values were in A1 and A2

=COUNTIF(D133:D164,{"<"&A1,">"&A2})