Count cells with "<Null>" string

[Prosperina]

I have this set of data and I'm trying to count the column B cells with " < Null > " (spaces not included) string, not an actual null value. I tried using countif(B:B," < Null > ") (spaces not included) but it returns the number of non-blank cells. It only returns the correct value when I add a random text on column B (used "sample"). The formula works on cells with alphanumeric values, though.

A B C
57822382 39.0m " < Null > "
57822383 22.0m " < Null > "
57822384 20.0m " < Null > "
57822385 16.0m " < Null > "
57822386 40.0m " < Null > "
57822387 44.0m " < Null > "
57822388 37.0m " < Null > "
57822389 28.0m " < Null > "
57822390 35.0m " < Null > "
57822391 32.0m " < Null > "
57822392 21.0m " < Null > "
57822393 31.0m " < Null > "
57822394 33.0m " < Null > "
57822395 37.0m " < Null > "
57822396 18.0m " < Null > "
57822397 18.0m " < Null > "
57822398 37.0m " < Null > "
57822399 33.0m " < Null > "
57822400 31.0m " < Null > "
57822401 39.0m " < Null > "
57822402 " < Null > " " < Null > "
57822403 " < Null > " " < Null > "
57822404 40.0m " < Null > "
57822405 " < Null > " " < Null > "
57822406 " < Null > " " < Null > "
57822407 43.0m " < Null > "
57822408 45.0m " < Null > "
57822409 " < Null > " " < Null > "
57822410 " < Null > " " < Null > "
57822411 43.0m " < Null > "
57822412 " < Null > " " < Null > "
57822413 17.0m " < Null > "
57822414 35.0m " < Null > "
57822415 38.0m " < Null > "
57822416 " < Null > " " < Null > "
57822417 " < Null > " " < Null > "
57822418 24.0m " < Null > "
57822419 24.0m " < Null > "
57822420 " < Null > " " < Null > "
57822421 " < Null > " " < Null > "
57822422 35.0m " < Null > "
57822423 " < Null > " " < Null > "
57822424 40.0m " < Null > "
57822425 41.0m " < Null > "
57822426 " < Null > " " < Null > "
57822427 " < Null > " " < Null > "
57822428 26.0m " < Null > "
57822429 " < Null > " " < Null > "
57822430 " < Null > " " < Null > "
57822431 50.0m " < Null > "
57822432 40.0m " < Null > "
57822433 " < Null > " " < Null > "
57822434 " < Null > " " < Null > "

 

[Michael M]

So....are the blank cells really blank.
In a cell adjacent any blank cell type

=len(B2)

or whatever the cell in B is....if it is greater than zero...it isn't blank.
Your formula works fine for me !!

 

[Rick Rothstein]

I have this set of data and I'm trying to count the column B cells with " < Null > " (spaces not included) string, not an actual null value. I tried using countif(B:B," < Null > ") (spaces not included) but it returns the number of non-blank cells.

Are the quote marks you show around the "<Null>" actually in the cells?

 

[Prosperina]

So....are the blank cells really blank.
In a cell adjacent any blank cell type

=len(B2)

or whatever the cell in B is....if it is greater than zero...it isn't blank.
Your formula works fine for me !!

It may be because I replaced the "< Null >" text in the data set with spaces in between. Otherwise, it will not show in the post.

 

[Prosperina]

Yes. The data I extract has the actual <Null> in its cell. I'm trying to count how many there are.

 

[Prosperina]

Are the quote marks you show around the "<Null>" actually in the cells?

Yes. The data I extract has the actual in its cell. I'm trying to count how many there are.

 

[Marcelo Branco]

Maybe

=COUNTIF(B:B,""" < Null > """)<null>
without the spaces

M.
</null>

 

[alansidman]

Easy with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column2] = " < Null > ")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1)
in
    #"Added Index"

 

[Prosperina]

Maybe

=COUNTIF(B:B,""" < Null > """)<null>
without the spaces

M.
</null>

Results return 0

 

[Prosperina]

Easy with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column2] = " < Null > ")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1)
in
    #"Added Index"

I'm not a macros user just yet but I will try to learn soon.