How to count cells in Excel

Question: I’m using Excel for some calculations, and I need to count some cells within a range. My problem is that while I’m fine using formulas such as, =COUNTIF(A2:A200,J5), I don’t know how to make the references such as A2:A200 change as the range of data changes. I’d like to be able to use a range name, or possibly a value contained in another cell in the spreadsheet.

The sort of thing I’d like to be able to do is to have, =COUNTIF(A2:A(DataCount),J5), where DataCount is a range name that identifies a cell reference somewhere on the worksheet, so if DataCount contained the value 300, the formula would work out to be, =COUNTIF(A2:A300,J5). My problem is that I don’t know how to achieve my aims. Can you help?

Ken Staveley

Answer: The easiest way to achieve what you need is using the INDIRECT function. What you need to do is to first create the correct form of cell reference. For your example, you’d use, “A2:A” & DataCount

This would combine the value in the cell that DataCount refers to with the text A2:A, and you’d end up with something like A2:A300 (if DataCount contained the value 300).

This formula is used with the Indirect function. Indirect does exactly what you want; it returns the reference specified by a text string, and the reference is immediately evaluated to display its contents. You use Indirect when you want to change the reference to a cell within a formula without changing the formula itself. This is then used as the input range for your CountIf formula, =COUNTIF(INDIRECT(“A2:A”&DataCount),J5).