Count Cells With Formulas in Excel

To count cells that contain formulas in Excel, you need a special approach because there is no built-in COUNTIF function that directly checks for formulas. The most reliable method is to use Excel’s Find and Replace feature or a combination of SUBTOTAL and GET.CELL for a dynamic count. This article explains both methods, how they work, and how to choose the right one for your situation.

Formula

The formula for counting cells with formulas using the Find and Replace method is simply the COUNT function applied to the range of cells that Excel identifies as containing formulas. In practice, you don’t write a single formula; instead, you use Excel’s search functionality to select all formula cells, and then you use COUNT on the selected cells. For example, after using Find and Replace to select all cells with formulas, you can type =COUNT(A1:A10) in a blank cell to count the selected cells (assuming the range is A1:A10).

For the SUBTOTAL method, the formula is:

=SUBTOTAL(103, range) – but this only counts visible cells, not specifically formula cells. To count only cells with formulas, you need to use a helper column with the GET.CELL function, which is a legacy Excel 4.0 macro function. The formula in a helper column (e.g., B1) would be:

=GET.CELL(48, A1)

This returns TRUE if cell A1 contains a formula, and FALSE if not. Then you can use COUNTIF to count the TRUE values:

=COUNTIF(B:B, TRUE)

Here, 48 is the code that tells GET.CELL to check if the cell contains a formula. The variable A1 is the cell you are checking. This method requires the helper column to be filled down for all cells you want to check.

Worked Example

Let’s say you have a worksheet with data in cells A1:A10. Cells A1, A3, A5, and A7 contain formulas (e.g., =B1*2), while the others contain numbers. You want to count how many cells contain formulas.

Method 1: Find and Replace

1. Select the range A1:A10.
2. Press Ctrl+F to open Find and Replace.
3. Click Options to expand the dialog.
4. In the Find what box, type = (without quotes).
5. Click the arrow next to Look in and select Formulas.
6. Click Find All. A list of all cells with formulas appears at the bottom.
7. Press Ctrl+A to select all those cells.
8. Close the dialog. In a blank cell, type =COUNT(A1:A10) and press Enter. The result is 4, because there are four formula cells.

Method 2: Helper Column with GET.CELL

1. In cell B1, enter =GET.CELL(48, A1).
2. Drag the fill handle down to B10 to copy the formula to B1:B10.
3. In cell C1, enter =COUNTIF(B1:B10, TRUE). The result is 4.

Both methods give the same answer: 4 cells contain formulas.

Why There’s No Built-In COUNTIF for Formulas

Excel’s COUNTIF and COUNTIFS functions are designed to count cells based on their values or formatting, not their content type. A formula’s result is what appears in the cell, so COUNTIF sees the result, not the formula itself. For example, if A1 contains =2+2 and displays 4, COUNTIF(A1, “4”) would count it because the value is 4, but there’s no direct way to say “count cells that contain an equals sign.â€� That’s why workarounds like Find and Replace or GET.CELL are necessary.

How to Use Find and Replace to Count Formulas

The Find and Replace method is quick and works on any version of Excel. It’s especially useful when you want to see which cells have formulas before counting. Here’s a step-by-step breakdown:

1. Select the range you want to search. If you want the entire sheet, click any cell and press Ctrl+A (once) to select all.
2. Open Find and Replace with Ctrl+F (or Ctrl+H for Replace).
3. In the “Find whatâ€� field, type an equals sign =.
4. In the “Look in� dropdown, choose Formulas. This tells Excel to search the underlying formula, not the displayed value.
5. Click Find All. Excel lists every cell that contains a formula.
6. Press Ctrl+A to select all listed cells. The status bar at the bottom of Excel may show “Count: Nâ€� where N is the number of selected cells. Alternatively, you can type =COUNT( and then select the range manually, but it’s easier to just read the status bar.

If you want a permanent count in a cell, you can use the COUNT function on the selected range, but you must know the range. A more elegant approach is to use the Name Box to create a named range of the selected cells, then count that range. For instance, after selecting the formula cells, type a name like “FormulaCellsâ€� in the Name Box (left of the formula bar) and press Enter. Then in any cell, use =ROWS(FormulaCells) * COLUMNS(FormulaCells) or simply =COUNTA(FormulaCells) (since all formula cells have values).

Using GET.CELL for a Dynamic Count

If you need a count that updates automatically as you add or remove formulas, the GET.CELL method is better. GET.CELL is a macro function from Excel 4.0, but it still works in modern Excel. It must be used in a named range or a helper column because it cannot be used directly in a regular cell formula without defining a name. Here’s how to set it up:

1. Go to the Formulas tab and click Define Name.
2. In the “Nameâ€� field, type IsFormula.
3. In the “Refers toâ€� field, enter =GET.CELL(48, INDIRECT("RC", FALSE)). This uses the current cell’s reference.
4. Click OK.
5. In your worksheet, in a helper column (e.g., B1), enter =IsFormula and drag down. This returns TRUE if the cell to the left contains a formula.
6. Then use =COUNTIF(B:B, TRUE) to count all TRUE values.

This method is dynamic: if you change a cell from a value to a formula, the helper column updates instantly, and the count adjusts. However, it requires a helper column and can be slower on very large sheets.

Common Mistakes and Pitfalls

• Searching values instead of formulas: If you forget to change “Look inâ€� to Formulas, Excel will search the displayed values, and you might miss cells that show a number but have a formula behind it.
• Counting blank cells: If a formula returns an empty string (e.g., =IF(A1="","",B1)), the cell appears blank but still contains a formula. Find and Replace with “Formulasâ€� will still find it, but COUNTIF might not if you use criteria like “<>â€� (not blank).
• Using COUNTIF with “=*â€�: Some users try =COUNTIF(A1:A10, "=*") to count formulas, but this counts cells that start with an equals sign as text, not formulas. It won’t work.
• Array formulas: If you have array formulas (entered with Ctrl+Shift+Enter), they still count as formulas, but Find and Replace may show them differently. It still works.
• Hidden rows: Find and Replace counts all cells, including hidden ones. If you only want visible formula cells, you’d need to use SUBTOTAL or filter first.

Alternative Methods for Counting Formulas

Besides the two main methods, there are a few other ways to count formulas:

• VBA macro: You can write a simple VBA function to loop through a range and count cells with HasFormula = True. This is the most flexible but requires enabling macros.
• Excel’s “Go To Specialâ€�: Press F5 (or Ctrl+G), click Special, then choose Formulas. This selects all formula cells in the current selection. The status bar shows the count. This is essentially the same as Find and Replace but faster for a quick look.
• Third-party add-ins: Some Excel add-ins offer a formula count feature, but they are not standard.

For most users, the Find and Replace method is sufficient. For a live count, the GET.CELL method is the way to go. VBA is overkill unless you need to integrate the count into a larger automation task.

Frequently Asked Questions

Can I count formulas in a single cell without a helper column?

No, Excel doesn’t have a built-in function that directly tells you if a cell contains a formula. The only way to get a dynamic count without a helper column is to use a VBA user-defined function, but that requires writing code. The GET.CELL method must be used in a named range or helper column because it’s a macro function.

Why does my COUNTIF not work for counting formulas?

COUNTIF works on values, not formulas. It sees the result of the formula, not the formula itself. For example, if A1 has =2+2 and displays 4, COUNTIF(A1, “4”) will count it, but COUNTIF(A1, “=2+2”) will not. There is no way to make COUNTIF check for the presence of an equals sign in the formula bar.

Does the Find and Replace method count formulas in hidden rows?

Yes, Find and Replace searches all cells in the selected range, regardless of whether they are hidden. If you want to count only visible formula cells, you need to filter the data first or use a SUBTOTAL-based approach with a helper column that marks formulas, then filter on that.

Can I use the GET.CELL method in Excel Online or Google Sheets?

No, GET.CELL is a legacy Excel macro function that only works in desktop versions of Excel. Excel Online does not support it. In Google Sheets, you can use a custom function in Apps Script to check for formulas, but there is no built-in equivalent.

Is there a way to count formulas in a pivot table?

Pivot tables do not count formulas directly. You would need to count formulas in the source data range before creating the pivot table, then add that count as a value field. Alternatively, you can use a calculated field, but that doesn’t count formulas.

What if my formula returns a #VALUE! error? Does it still count?

Yes, a cell with a formula that returns an error is still considered a formula cell. Both Find and Replace and GET.CELL will count it. If you want to count only formula cells that do not have errors, you’d need to combine the count with an error check, such as =COUNTIF(range, "<>") but that’s not straightforward.

Related

• Samsung SRF7300SA: What to Know Before Buying
• iPhone X Battery Percentage: How to Show It
• Garden Horizons Codes & How to Redeem Them

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2