Excel: Counting Hard-coded text vs. formula with same result

April 16, 2012 at 11:54:26
Specs: Windows XP
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!


See More: Excel: Counting Hard-coded text vs. formula with same result

Report •

#1
April 16, 2012 at 11:58:19
Delete this, won't work.

MIKE

http://www.skeptic.com/


Report •

#2
April 16, 2012 at 12:02:01
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

Report •

#3
April 16, 2012 at 12:06:53
Why does it matter, just drag down the formula and overwrite the hard coded "Yes"

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 16, 2012 at 12:07:20
Maybe the simplest way to generically ask is can I count how many cells in a range contain formulas versus manually-entered text?

Report •

#5
April 16, 2012 at 12:13:09
Why does it matter, just drag down the formula and overwrite the hard coded "Yes"

MIKE

Mike, 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


Report •

#6
April 16, 2012 at 12:25:49
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

http://www.skeptic.com/


Report •

#7
April 16, 2012 at 12:55:25
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.


Report •

Ask Question