# Thread: COUNTIF with AND condition (2003)

1. ## COUNTIF with AND condition (2003)

I am trying to do a countif to find a range where the value falls between certain amounts. Example, range 'fee2' where the value is greater than \$1M and less than \$2M. So far my formula has failed: =COUNTIF(fee2,AND(">=1000000","<2000000")). Does anyone know of a way to do this? Thank you very much, Norma

2. ## Re: COUNTIF with AND condition (2003)

This is done using a SUMPRODUCT formula or an array formula with SUM:

Possibility 1:

=SUMPRODUCT((fee2>=1000000)*(fee2<2000000))

Possibility 2 - array formula (confirm with Ctrl+Shift+Enter):

=SUM((fee2>=1000000)*(fee2<2000000))

3. ## Re: COUNTIF with AND condition (2003)

Thank you SO MUCH! It's brilliant, Norma

4. ## Re: COUNTIF with AND condition (2003)

(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

I am also looking for a solution to count values within a selected range that meet certain criteria, so I thought =COUNTIF(G10:G75,AND(">0","<=249.99")) looked like a solution. Apparently not. That's why I turn to Woody's Lounge.
I have found an article at Tech Republic (can I say that here?) http://articles.techrepublic.com.com/5100-...11-6073927.html that explains the use of a custom function.

In a VB module in the workbook:

Function COUNTBETWEEN(rng, num1, num2)
COUNTBETWEEN = Application.CountIf(rng, "<=" & num2) - Application.CountIf(rng, "<" & num1)
End Function

The cell entry is: =COUNTBETWEEN(H1:H400,10,20)

But I can only get the results to appear once, at the moment I press enter, having just typed in the formula. The article does not explain how to refresh and F9 and Alt+F9 don't seem to force a recalc.

Can you offer a solution to this solution?

5. ## Re: COUNTIF with AND condition (2003)

If calculation is set to automatic in the Calculate tab of Tools | Options..., the result of the formula =COUNTBETWEEN(H1:H400,10,20) should update itself automatically as you change values in the range H1:H400. But make sure that the formula is not in a cell in the range H1:H400 - that would lead to a circular reference.

Oh, and there is no objection to mentioning other discussion sites such as TechRepublic! We all get our information where we can. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

6. ## Re: COUNTIF with AND condition (2003)

Thanks Hans. COUNTBETWEEN now works great. (I flubbed my criteria.)

But the first two Possibilities you offered, SUMPRODUCT and SUM array work fine too. And they don't require opening a module.

7. ## Re: COUNTIF with AND condition (2003)

In general, solutions involving only built-in functions are to be preferred, since they operate much faster than VBA code. But if the formulas become so complex that they become unwieldy, or what you want cannot be accomplished using built-in functions, a custom VBA function is a good alternative.

8. ## Re: COUNTIF with AND condition (2003)

Since the COUNTBETWEEN UDF simply uses the COUNTIF worksheet function, I would just use that in the worksheet directly rather than having to worry about macro warnings and users disabling macros. But obviously there are times when a UDF is the best way to go.

9. ## Re: COUNTIF with AND condition (2003)

Hi Arcturus16a,

You could also use:
=COUNTIF(G10:G75,">0)-COUNTIF(G10:G75,">249.99")
Not being an array formula (which SUMPRODUCT is even though you don't enter it as one), it should calculate faster. Neither does it rely on a UDF.

10. ## Re: COUNTIF with AND condition (2003)

Hi nthayer,

Regarding your original formula, you can make it work with:
=COUNTIF(fee2,"<2000000")-COUNTIF(fee2,"<1000000")
Not being an array formula (and this includes SUMPRODUCT) it should calculate faster.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•