Hi, I found we used to use IF() to make a cell like: =IF(A1>0, "Positive", "").
For this one, the cell will have a blank text "", instead of a truly empty cell with no text. Can I ask how to make a cell a real empty one? So that when we count these cells, they are excluded.
Thanks for teaching.
=IF(A1>0, "Positive",0)
And with format color you can hide the 0.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
oeldere, I'm sorry this does not work as 0 is still a value, not empty one. My question is how to make this really blank: not text, not number...
What you wanna do with the result.
You want to count them?
I think you can count them if the result is "".
Otherwise add an excel file of your data, without confidentional information.
P.s. VBA can also do that for you wiht clear content.
I can't help you enough with VBA.
If I count with the function COUNTA(), then a problem comes: this function also counts those cells with "" values. I just want to exclude those cells when counting. I am thinking to a way to do this is to first make these cells with "" values empty, and then use COUNTA(). Is there a way to achieve this?
Thanks!
maybe you can try this
countif(a1:b25",<>""")
Last edited by oeldere; 10-31-2012 at 08:16 PM. Reason: I changed the formula
Sorry, not working. Excel says this formula has an error.
Originally Posted by oeldere
countif(a1:b25,<>""")
I changed the "" after 25
Can you give a couple of rows of data and what your expect?
HTH
Regards, Jeff
=sumproduct(--(a1:a10<>"")) excludes cells containg null strings.
Entia non sunt multiplicanda sine necessitate
This approach counts those cells with text values. What if I want to count both text and numeric values and ignore "" (blank text)?
This worked!!! Very good knowledge.
That's what the formula does.What if I want to count both text and numeric values and ignore ""
I did some research, and it may be true that in simple Excel (not VBA), we can never make a cell truly empty with no values, without physically delete it.
@shg,
I was going to offer...
=SUMPRODUCT(--(LEN(B1:B10)>0))
...and it seems to produce what your formula does, so would you see this as another option?
Sure. It does add a function, though.
I notice that as long as a cell has some contents (e.g., a formula used to make it as blank text ""), then functions like =ISBLANK() will be not useful. That's why I was thinking to find a way to make the cell really an empty one. But seemingly that there is no way to do it.
How can you make the cell really empty when there is a formula in it?
If you are not getting across what you want us to help with, then maybe a sample workbook with what you have and what you expect.
If we say, cell A1 has a value of 5, then for cell B1, can we do it like to make B1 an empty one: =IF(A1=5, truly empty, "value there in A1")How can you make the cell really empty when there is a formula in it?
If you are not getting across what you want us to help with, then maybe a sample workbook with what you have and what you expect.
This may be a logic, but no way to do it? Sorry for this 'insanity'.
I've been on this forum for a few days now and been working with formulas for just about as long.
With you description in post #19, =IF(A1=5,"","value there in A1")
I'm sorry but outside of this I just don't know what you are searching for or why.
That also helped! At least I am assured that the only way we can do is to make it something like null strings "", rather than a 'blank' cell. Thanks a lot for your great advice.
You are very welcome. Thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks