Count the number of cells a formula references

[domtrump]

I have a formula that sums up specific cells above it in the column. (i.e. a50=a1+a7+a8+a13...). Sometimes we take one of these cells out of the mix due to specific circumstances. I need to create a cell that indicates the number of cells being referenced by this formula for each column so that we know that a special condition is being applied. Is there some way to COUNT the number of cells being referenced by another formula?

Thanks.

 

[Akashwani]

Hi,

Take a look here.....
Show FORMULA or FORMAT of another cell

And then try something like this....

Excel Workbook
	A	B	C	D
1	Sum	Formula Used	Result
2	893.00	=B5+B10+B15	3
3
4	Name	Amount
5	Bart Simpson	368.00
6	Lisa Simpson	277.00
7	Bart Simpson	474.00
8	Lisa Simpson	163.00
9	Maggie Simpson	346.00
10	Homer Simpson	100.00
11	Homer Simpson	147.00
12	Bart Simpson	267.00
13	Maggie Simpson	299.00
14	Homer Simpson	399.00
15	Bart Simpson	425.00
16	Bart Simpson	496.00
17
Results

You will need to use....
=GetFormula(A2)
In one cell to display the sum formula and then in a different cell use.....
=LEN(B2)-LEN(SUBSTITUTE(B2,LEFT(SUBSTITUTE(B2,"=",""),1),""))
to count the number of cells used in your original sum formula.

Or you could join it all together in one cell.....
=LEN(GetFormula(A2))-LEN(SUBSTITUTE(GetFormula(A2),LEFT(SUBSTITUTE(GetFormula(A2),"=",""),1),""))

I hope that helps.

Good luck.

Ak

 

[Rick Rothstein]

I have a formula that sums up specific cells above it in the column. (i.e. a50=a1+a7+a8+a13...). Sometimes we take one of these cells out of the mix due to specific circumstances. I need to create a cell that indicates the number of cells being referenced by this formula for each column so that we know that a special condition is being applied. Is there some way to COUNT the number of cells being referenced by another formula?

As long as your formula looks like you showed us (highlighted in red above) with individual plus signs between individual cell references, you can use this UDF to count how many cell references there are...

Function CellsBeingSummed(R As Range) As Long
  Application.Volatile
  CellsBeingSummed = Len(R.Formula) - Len(Replace(R.Formula, "+", "")) + 1
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CellsBeingSummed just like it was a built-in Excel function. For example,

=CellsBeingSummed(A50)

 

[AAE]

Tracing Cell Precedents in VBA


How can a VBA macro find which cells are being referenced in a formula

 

[erik.van.geit]

Hi,
You can do this without any VBA if you like.
reference: http://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html

Select cell B1
Define a name
CellFormula (or whatever name you like)
Formula: =GET.CELL(41;A1)

Now you can use in any cell 1 column to the right of your formula
=LEN(CellFormula) - LEN(SUBSTITUTE(CellFormula, "+", "")) + 1

kind regards,
Erik

 

[Akashwani]

Hi,

Erik, I do like the non VBA solution.

Just out of curiosity folks
What would be the best solution if the SUM were a range, eg..
=SUM(A2:A20)

Any ideas?

Ak

 

[erik.van.geit]

assuming you have only =SUM(yourrange), you can use
=ROWS(INDIRECT(MID(CellFormula,SEARCH("(",CellFormula)+1,LEN(CellFormula)-SEARCH("(",CellFormula)-1)))*COLUMNS(INDIRECT(MID(CellFormula,SEARCH("(",CellFormula)+1,LEN(CellFormula)-SEARCH("(",CellFormula)-1)))
This is multiplicating rows and columns.

I would prefer though to use a helper column for the cellformula and the search instead of this long formula, especially if you need a lot of them.

If all cells are populated with numbers, you can use
=COUNT(INDIRECT(MID(CellFormula,SEARCH("(",CellFormula)+1,LEN(CellFormula)-SEARCH("(",CellFormula)-1)))

 

[Akashwani]

Thanks for that Erik.

I'm going have to save a link to this thread for future reference.

Ak