I have a large list of data that may or may not contain a formula. I need to get a count of which cells have formula results, versus the ones that were manually overridden by the same text. For example: Column A contains a long list of text. Column B has =IF(left(A2,3)="Z31","Yes","No"). That formula is repeated down 30000 or so rows. Now, some people have manually overwritten the formula result by just typing "Yes" in a bunch of cells. I need a formula to count how many of those exist.

Thanks for your help!

Mike, thanks for the quick reply, but this doesn't work because the results of the formula also contain "Yes" and therefore show up in the count. I need to only count the cells that have "Yes" as a result of being manually typed in the cell, versus how many cells contain "Yes" as a result of the formula. Thanks

Why does it matter, just drag down the formula and overwrite the hard coded "Yes" MIKE

Maybe the simplest way to generically ask is can I count how many cells in a range contain formulas versus manually-entered text?

Why does it matter, just drag down the formula and overwrite the hard coded "Yes"

MIKEMike, I wish it were that easy, but there is actually a difference between the "Yes" versus formula-"Yes" results which (unfortunately) I need a way to count. It feeds into another set of data which relies on knowing how many cells are formulas. Annoying, I know, but I have to solve it. Thanks

~~Ok, a quick and not real elegant way,~~Open a new column next to column B

and in your new Column C, enter the formula:=CELL("contents",B1)

Drag down

If a cell has a formula, it will remain blank

if a cell has actual text, it will display the text, in your case the word "yes"Do a =COUNTIF(C1:C30000,"yes")

~~See how that works.~~No, that's not going to work either, your going to need some VBA to get it to work.

Sorry my VBA skills are minimal at best.

MIKE

This User Defined Function (UDF) will return the count of cells within the specified range that do not contain formulas: Function notFormula(Check_Range As Range) For Each cell In Check_Range If Not cell.HasFormula Then tempCount = tempCount + 1 Next notFormula = tempCount End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Adobe should discontinue Flash?

Discuss in The Lounge

Poll History