Not count "Blank cells"

[gspot]

Hi!
I have this formula applied to a column:
=TEXT((IF((NOT(ISBLANK(D5)));(IF((COUNTIF(D:D; A5))=1;"";"Tratadas"));""));"")

Excel will count all the cells with formulas, including those that look empty, are there any way to change that? I dont wont to count the blank cells...


Thanks

 

[JamesW]

Hi,

Try:

=TEXT((IF((D5 < > ""),(IF((COUNTIF(D1:D12, A5))=1,"","Tratadas")),"")),"")

D5 wont be truely blank if you have a formula that sets the value to "", it may look blank but Excel thinks it's not

Change ,'s to ;'s

 

[VoG]

Instead of NOT(ISBLANK(D5)) try D5<>""

 

[T. Valko]

Hi!
I have this formula applied to a column:
=TEXT((IF((NOT(ISBLANK(D5)));(IF((COUNTIF(D:D; A5))=1;"";"Tratadas"));""));"")

Excel will count all the cells with formulas, including those that look empty, are there any way to change that? I dont wont to count the blank cells...


Thanks

Can't see why you're using the TEXT(...) function.

Maybe this...

=IF(AND(D5<>"";COUNTIF(D:D;A5)=1);"";"Tratadas")

 

[DonkeyOte]

Perhaps

=IF(COUNTBLANK(D5)=0;IF(COUNTIF(D:D;A5)>1;"Tratadas";""))

 

[gspot]

Can't see why you're using the TEXT(...) function.

Maybe this...

=IF(AND(D5<>"";COUNTIF(D:D;A5)=1);"";"Tratadas")

Your correct, the TEXT function doesn’t do anything it was a “experiment”… the correct function is without TEXT:
IF((NOT(ISBLANK(D2)));(IF((COUNTIF(D:D; A2))=1;"";"Tratadas"));"")
I have tried changing to D5<>"" and the function works but still counts the blank cells


thanks for the replies

 

[T. Valko]

Your correct, the TEXT function doesn’t do anything it was a “experiment”… the correct function is without TEXT:
IF((NOT(ISBLANK(D2)));(IF((COUNTIF(D:D; A2))=1;"";"Tratadas"));"")
I have tried changing to D5<>"" and the function works but still counts the blank cells


thanks for the replies

Are you saying that this portion of the formula counts blank cells:

COUNTIF(D:D;A2)

What's in A2?

 

[gspot]

Are you saying that this portion of the formula counts blank cells:

COUNTIF(D:D;A2)

What's in A2?

The countif works fine, the result of my formula:IF((NOT(ISBLANK(D2)));(IF((COUNTIF(D:D; A2))=1;"";"Tratadas"));"") Is the problem, some cells appear blank but Excel assumes as having something....

 

[Relysis]

If these cells are supposed to be blank, you may need to filter your table for blanks, select the column, then clear contents. Then the blanks should be seen correctly by Excel.

 

[T. Valko]

The countif works fine, the result of my formula:IF((NOT(ISBLANK(D2)));(IF((COUNTIF(D:D; A2))=1;"";"Tratadas"));"") Is the problem, some cells appear blank but Excel assumes as having something....

OK, now I understand...

Well, that's just how Excel works.

If a cell contains a formula then that cell is not blank even if the formula returns the empty text string "".

If you need to count cells that contain TEXT but exclude cells with formula blanks try this:

=COUNTIF(range;"?*")