Excel Count If Function with additional range

Question

Here is a COUNTIF function with an additional range of values to match at the end. Why does it not work and how can I get it to work.

=COUNTIF('[DATABASE 1.xlsx]Sheet1'!$AC$3:$AC$51877,AND('[DATABASE 1.xlsx]Sheet1'!$AC$3:$AC$51877>=548,'[DATABASE 1.xlsx]Sheet1'!$AC$3:$AC$51877<=554)

barry houdini · Accepted Answer · 2012-03-30 18:01:28Z

3

If you can't use COUNTIFS you subtract one COUNTIF from another like this

=COUNTIF('[DATABASE 1.xlsx]Sheet1'!$AC$3:$AC$51877,">=548")-COUNTIF('[DATABASE 1.xlsx]Sheet1'!$AC$3:$AC$51877,">554")

that counts between 548 and 554 inclusive

answered Mar 30, 2012 at 18:01

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nice trick! particularly useful for Excel version prior to 2007

JMax · Accepted Answer · 2012-03-30 16:14:20Z

2

You cannot use COUNTIF with AND because the second part must be a criteria.
Plus, the second part (criteria) shouldn't repeat the range.

You can use COUNTIFS if you are using Excel version 2007 or higher. Else, you can use an array formula.

Something like this maybe:

=COUNTIFS('[DATABASE 1.xlsx]Sheet1'!$AC$3:$AC$51877,">=548",
          '[DATABASE 1.xlsx]Sheet1'!$AC$3:$AC$51877,"<=554")

answered Mar 30, 2012 at 16:14

JMax

26.7k12 gold badges74 silver badges89 bronze badges