Excel: COUNTIF function treats 'less than' character as an operator

excel worksheet-function libreoffice
Lars picture Lars · Sep 26, 2013 · Viewed 7.8k times · Source

Pre-read note: I'm using LibreOffice rather than Excel, but most functions should apply to both.

I'm making a spreadsheet where I have a bunch of data, and for every property (such as # of employees or a name) I need a function that calculates how many rows contain each distinct value.
I already have the distinct values extracted, and now I'm using the following function to calculate how many rows have each value: =COUNTIF(start:end;value). (Note that the semicolon is LibreOffice's way of separating parameters, as opposed to Excel's comma.)

Now, my problem is that I have some rows that can have values such as "< 50". Using the COUNTIF function, it seems that the '<' is treated as a 'less than' operator, rather than as a text character. Therefore, if the cell I'm trying to match the column against has the value "< 50", I end up with a value 0 as result of the formula. How can I get the COUNTIF to treat the less-than sign as a text character? Or is there another function I should use here?

Answer

user2140261 picture user2140261 · Sep 26, 2013 
=COUNTIF(A:A,"=< 50")

Works for me in Excel 2007

enter image description here

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More