CODEWITHSUNDEEP
excelexcel-formula
Walrus
Walrus

Reputation: 20444

Excel Count If Function with additional range

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)

Upvotes: 1

Views: 1663

Answers (2)

barry houdini
barry houdini

Reputation: 46361

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

Upvotes: 3

JMax
JMax

Reputation: 26591

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")

Upvotes: 2

Related Questions