• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count number of times a cell reference appears within formulae in a range

k1s

Member
Hi Guys,

How can I count the number of times a cell reference appears within formulae in a range?
In each of cells G85:96 I have a simple CountA formula like these:

in G85 =COUNTA(B21,B43,B44,B45)
in G85 =COUNTA(B21,B32,B44,B51)

...in which I have manually selected those cells in column B.

What I'd like to do in say H21 is have a count of the times that cell B21 appears in formulae in the range G85:96.

I've been reading about search and find, but not getting very far. Guidance much appreciated!
 
Depends on your Excel version.

Try...
=COUNT(FIND("B21",INDEX(FORMULATEXT(G85:G96),)))

Confirmed as array (CTRL + SHIFT + ENTER)
 
One note: If you have range beyond B200 in formula text. You'd probably want to find "B21," instead of just for "B21". As "B210" to "B219" also will be counted when "B21" is used.
 
Back
Top