how to make a cell empety?

[wigarth]

Hi!

I have a sheet that uses data from many other sheets. I.E. IF(A1="";"";A1)

Later on i get in trouble when i use the COUNTA formula... because Excel somehow thinks that "" is a blank space and counts that as a letter. This makes my "COUNTA" go nuts... (me as well)
Any other expressions than "" I can use? (0 and autoformat is a no-go!)

Vast amount of data and cells in original file, so changing the original sheets are... let's call it impossible

Appreciate any help on this.
Best Reggards
Wig!

 

[SpillerBD]

COUNTA counts non-empty cells. A cell with a formula will never be empty.
You will need to look at COUNTIFS or perhaps a SUMPRODUCT function instead.

 

[cerfani]

use countif then...

=COUNTIF(...,"<>""""")

a " inside a string needs to be escaped so "" = "... the criteria will resolve to <>""

 

[jtakw]

Hi,

You didn't specify what's in the cells that you DO want counted, is it:
All cells that have any Text and/or Numbers? Only Numbers? Only Text?
This will give you a count of All cells that have any valid data, ignores "" from result of formulas:

Book1
	A	B	C
1	1		8
2
3	a1
4	2
5
6	b
7	3a
8	4
9	0
10	d
Sheet23

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(--(LEN(A1:A10)>0))
A2	=""

 

[wigarth]

Thank you! this worked right away. You are a real life saver!!

 

[jtakw]

You're welcome, glad it worked for you.