Why are cells containing formula's counting as having string in them?

1
votes

I've got a fairly simple question and I'm sure that I'm missing something obvious.

I've got say 40 cells and all of them contain a formula in them. Only 38 of those cells actually have string or text in them the remaining two do NOT. They're blank with the exception of the formula.

However when I do a COUNTIF or a COUNTA to try and not count the cells that are filled automatically it is giving me the result of 40.

Ways I've tried this and all go the result of 40:

=COUNTIF(B60:B99,"*")
=COUNTIF(B60:B99)
=(COUNTA(B60-B99)

Does anyone know what I'm doing wrong?

Example of the formula in a blank cell that is being counted:

=IF(ISBLANK('Dodgeball'!B48),"",'Dodgeball'!B48)
3
excelformulas
Can you post an example of the formulas that you are counting? – Degustaf
I've provided the formula above. – Motorhead1308
Is =COUNTIF(B60:B99,"<>*") what you are looking for? – BrakNicku
@user3964075 that gave me a return of 0. I'm just looking to count all of the 40 cells that aren't blank. There are 38 cells that aren't blank but I have multiple files and don't want to do this manually each month. – Motorhead1308

3 Answers

2
votes

Use:

=SUMPRODUCT(--(B60:B99<>""))

as this ignores null strings.

0
votes

You are attempting to count the cells that are not "" but considering them as blank. Try using

=countif(B60:B99,"<>""""")

To explain that final string of quotes, the first one is an escape character so that the second one is read as quotes within the string, similarly the third and fourth are an escape character and a quote for within the string, and finally we end the string with a quote.

0
votes

One option would be to insert an additional column and simply use the istext() function. Then you can sum that column to get your text count, because false counts and 0 and true counts as 1.