Forum Discussion

Lauren565's avatar
Lauren565
Copper Contributor
Aug 09, 2022

Function Help

I’m trying to count some data.

 

The column I’m trying to count has a lot of different data. 

 

EX columns:

xxxx_yyyy_zzz:3, xxxx_yyyy_uuu (has 1 value)

ssss_www_iiii:2, ssss_www_ppp:1 (has 2 values)

 

The data I’m trying to count how many that have a value (:1) and the ones that don’t in the same line. For example, the first line only has 1 value (:1)but also has one without a value. I have the count down for the ones with a value with the function

 

=countif(I:I, “*yyyy*” & “*:*”) 

 

How can I count the ones without a value while being in the same line?

 

excel

3 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Aug 10, 2022

    Lauren565 If seems you are just trying to count how many colons ":" do you have in same line (in a cell). If so, then use below SUBSTITUTE() function.

    =LEN(I1)-LEN(SUBSTITUTE(I1,":",""))

     

    • Lauren565's avatar
      Lauren565
      Copper Contributor
      Aug 10, 2022

      Harun24HR I’m having no problem counting the colons, I’m trying to count the once’s without the colons

      • Harun24HR's avatar
        Harun24HR
        Bronze Contributor
        Aug 10, 2022
        Can you put a screenshot of your excel and show what result you want? For first line you have one word that doesn't have colon but second line your both word has colon. So, what would be your output from these two line?

Resources