Using COUNTIF Function to count formulas, not values

[gymkb]

I am trying to count the number of RTD calls in a given worksheet. The formula that I used was =COUNTIF(A1:FF1000, "*server*"). Server is a particular word in the RTD call that is exclusive to any RTD call in my spreadsheet. The problem is that the countif function does not see formulas, it only sees that the cell is blank (if macros not enabled) or that the cell has the value off the server (if macros are enabled). How can I get excel to consider formulas programmed into each cell, not the actual displayed value?

if I can't do it that way, is there a way I can literally tell excel, count this cell if it is an RTD call?

Thank you so much for any help!

 

[Scott Huish]

Hi, welcome to the board!

What does your formula have to do with macros?

What would be the value in the cell if it wasn't an RTD call (whatever that is)?

 

[gymkb]

Thanks!

My question does not necessarily have to do with macros, it is just that the values come off a server. So if the macros are enabled, the cell will have the value it is supposed to (in this case a temperature). If the macros are not enabled, the cell will be blank with a little green tab on the top corner of the cell, reminding you that the values are not available because you did not enable macros.

For the cells that I need the RTD calls (real time data), the formula entered into the cell is =1*RTD("server"..... etc etc). And that just enables us to upload data onto a server and have the spreadsheet display the values in a nice way. So I guess my question can really work for any formula. Lets just say I had a cell programmed that was =AVERAGE(A1:A5). So that will return whatever the average of those cells were. But if I wanted to find all the cells within that spreadsheet that were calculating averages, I would need to use a countif statement, and somehow tell it that I want all the cells counted that are computing an average. It is the same thing that i want to do with my RTD calls cells. Does that make it clearer?

 

[Scott Huish]

So, couldn't you just count the cells that have a number?

=COUNT(A1:FF5)

 

[shg]

Wouldn't it be easier to do Ctrl+F (Find), server, press Find All, and then look at the message at the bottom of the dialog: 27 cell(s) found

 

[gymkb]

The CTRL+F method seemed to work! I did not realize that you could do that in this case. Thank you very much.

I would still like to know though if there is some sort of way you can more "officially"compute this number using Excel functions. It would definitely be a good thing to know if anyone knows a way around my issue....

 

[gymkb]

And HOTPEPPER, I could not simply count cells in the worksheet that have numbers because there are other cells in the worksheet with numbers that are not RTD calls. Thanks for your suggestion though!

 

[shg]

You'd have to use VBA. Native Excel functions look at the values in the cells they reference, not the formulas that produce them.