Count How Many Cells are Referenced in a Formula in Excel

We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)

A viewer reached out wanting to know how he can count how many cells are referenced in a formula in Excel. Was this even possible???

For example, say he has a formula like AVERAGE(A1,A2,A3) in cell A4. What formula could he use to let him know that 3 cells were referenced?

I thought about this for a while and realized that, as long as each cell in the formula is listed individually, it’s possible to calculate the number of cells referenced by looking at how many commas were used.

Count the Number of Cells Used in an Excel Formula

Excel spreadsheets can quickly become overwhelming. Especially if you have to make changes to formulas. Deciphering which cells are used in a formula can get confusing fast!

Here’s the formula I recommended for counting the number of cells in the formula:

=LEN(FORMULATEXT(A4))-LEN(SUBSTITUTE(FORMULATEXT(A4),”,”,””))+1

It looks overwhelming, I know! But it’s really not. It combines three Excel formulas to, essentially, calculate:

(Total Number of Characters – Total Number of Characters Without Commas)+1 

Here’s how it works:

• =LEN(FORMULATEXT(A4))-LEN(SUBSTITUTE(FORMULATEXT(A4),”,”,””))+1:
  • The formula uses a combination of the LEN function (counts number of characters) and the FORMULATEXT function (converts cell contents to text) to count the number of text characters in the formula that you’re trying to audit.
    • Essentially, answering the question: if the formula was all text, how many characters does it have?
• =LEN(FORMULATEXT(A4))-LEN(SUBSTITUTE(FORMULATEXT(A4),”,”,””))+1:
  • It then uses the SUBSTITUTE function (like the Find & Replace feature, but in a function) to replace all the commas in the cell with nothing (not even a space).
• =LEN(FORMULATEXT(A4))-LEN(SUBSTITUTE(FORMULATEXT(A4),”,”,””))+1:
  • Next, it uses the LEN function again to count the number of characters in the new text (without the commas) and subtracts this from the original count of characters.
• =LEN(FORMULATEXT(A4))-LEN(SUBSTITUTE(FORMULATEXT(A4),”,”,””))+1:
  • Lastly, add 1 at the end to make up for the first cell reference (that never has a comma in front of it).

Want to try it for yourself? You can use this spreadsheet to experiment with the formula.

FYI, this was created using the desktop version of Excel in Microsoft 365.

---