Countif text not recognized

pleeseemailme

Board Regular
Joined
Dec 26, 2013
Messages
201
Howdy,

I have a report that spits out batches of information into a csv file. Before each batch, the report inserts the text: ">>>". I need to count the number of batches in each report, so I tried: =COUNTIF(A:A,">>>"). For whatever reason, this actually counts each cell with a value in it and not just the cells with ">>>". I've gotten around it by using the array formula: =SUM(IF(A:A=">>>",1,0)). However, I was curious if anyone could shed some light as to why the COUNTIF formula doesn't work; I'd like to learn why. Thank once again to you all.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Howdy,

I have a report that spits out batches of information into a csv file. Before each batch, the report inserts the text: ">>>". I need to count the number of batches in each report, so I tried: =COUNTIF(A:A,">>>"). For whatever reason, this actually counts each cell with a value in it and not just the cells with ">>>". I've gotten around it by using the array formula: =SUM(IF(A:A=">>>",1,0)). However, I was curious if anyone could shed some light as to why the COUNTIF formula doesn't work; I'd like to learn why. Thank once again to you all.

Consider:
FALSE
FALSE
<
FALSE
FALSE
>>>
FALSE
TRUE
s
TRUE
TRUE
x
TRUE
TRUE
3
FALSE
FALSE

<TBODY>
</TBODY>

[1] B1, copied down:

=A1 > ">>>"

[2] C1, copied down:

=A1 > "="&">>>"

[3]

=COUNTIF(A:A, ">>>")

===> 3

[4]

=COUNTIF(A:A,"="&">>>")

===> 1

COUNTIF behaves the same as D-functions...

If you want eliminate the effect of the > comparison operator, you need to invoke the COUNTIF formula with an = operator.

The behavior in [3] is copmparable with:

=COUNTIF(A:A, ">>")

or

=COUNTIF(A:A,">"&">")

or

=COUNTIF(A:A,">"&">>")

Conclusion: You need to neutralize the effect of the > operator.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
This is a guess.

The > is the operator greater than.

I think the COUNTIF function is interpreting this ">>>" as counting all cells as greater than ">>"
The first > is interpreted as an operator.

Try this instead
=COUNTIF(A:A,"=>>>")
 

pleeseemailme

Board Regular
Joined
Dec 26, 2013
Messages
201
That did it. I just needed the "=" sign. It makes sense now that it was looking for cells greater than ">>". Thanks so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,354
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top