COUNTIF is not picking up a ":"

[DashboardNovice]

My COUNTIF formula is not picking up a colon in cell A3:

=COUNTIF(A3,":")

There is text on both sides of the colon. The data was downloaded from QuickBooks and the cell is text format. It returns a 0.

What is wrong with my formula?

 

[bobhc]

Try =COUNTIF (A3, "*")

 

[DashboardNovice]

Try =COUNTIF (A3, "*")

This worked for the current cell, but not for subsequent cells which had more than one colon.

 

[eibi]

Hello,

What about this:

=SUM(--(LEN(SUBSTITUTE(B1:B4,":",))<LEN(B1:B4)))

where B1:B4 is the range in question. Executed as an array, by using Ctrl+Shift+Enter instead of Enter alone.

There's probably a shorter way to do this...

 

[eibi]

If you are not counting a range of multiple cells -- and you only want to return a 1 or 0 value, depending on whether A3 includes ":", you could:

=--(LEN(SUBSTITUTE(A3,":",))<LEN(A3))

or even

=IFERROR(FIND(":",A3)^0,0)

without CSE.

 

[Krishna Kinker Singh]

Dear DashboardNovice,

Your question is not clear to me please give your worksheet/example if possible.

Thanks,

Krishna

 

[Khalid NGO]

Hi,
I think COUNTIF will not work the way you are trying...
Try:
=SEARCH(":",A3)

or go with eibi..

Regards,

 

[Faseeh]

Please try this:

=COUNTIF(A3,"*"&":"&"*")

 

[Colin Legg]

My guess from the OP response is that the requirement is to count the number of colons which occur in a range.

@DashboardNovice
COUNTIF, such as the formula suggested by Faseeh, will count the number of cells which contain a ":". If you want to count the actual number of colons then you can use a formula such as:

=SUMPRODUCT(LEN(A3:A100)-LEN(SUBSTITUTE(A3:A100,":","")))


This formula assumes A3:A100 contains text data types.