Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

If someone can help improve the title, by all means. I've been struggling with it for a while...

I'm using Excel to analyze a list of filenames for potential issues...special characters, excessive length, etc., before we run them through a script. Right now, I've got a column to test for the condition and a COUNTIF to count the occurences of the test results.


COL A          COL B                                COL C
filename       =IF(LEN($A1)>200,"LONGNAME","")      =COUNTIF(B1:B10000,"LONGNAME")

I would like to simplify this to something that's just COUNTIF(anythingInColumn,isLong) without having to create a separate column to test for whether or not something "is long".


COL A          COL B                               
filename       =COUNTIF($A,LEN($A>200))

The problem with the above is that I see nothing that explicitly tests line-by-line for my condition, when I tried it, there did not seem to be an implicit line-by-line test. I

Is something like this doable, or is there another function to do this in a single step?

share|improve this question
up vote 3 down vote accepted

You should be able to use an array formula: type the following into C1, and press Ctrl-Shift-Enter:


You need the IF to convert Boolean to (0,1), and the SUM to count them all. (You can use A:A instead of an explicit row number, but it might be slower.)

share|improve this answer
I like it...but I'm getting a value of either 1 or 0. =SUM(IF(LEN(A1:A10000)>200,1,0)) gives a result of zero. If I look for >1 a 1 is returned. Spot checking other values it seems like everything else returns a 0. Ideas? – dwwilson66 Sep 18 '13 at 15:21
Sure you remembered to press Ctrl-Shift-Enter? It works for me for some random text. – benshepherd Sep 18 '13 at 15:25
Yep, I did...I'll keep mucking about... – dwwilson66 Sep 18 '13 at 15:46

There is a way, using SUMPRODUCT to add up the values, and -- to convert True/False into 1/0.

The equation in your case would be:


and there's no array formula required, as sumproduct is expecting an array.

Excel evaluates the LEN(A1:A10000)>200 as a 10,000 item list of True/False.
The -- changes the list of True/False to a list of 0/1
The SUMPRODUCT adds up all the 1's (no product, as we're only supplying a 1 dimensional array.

Note that this method could also be used to check multiple criteria, and only count the items where both are true (AND) - e.g. Filenames that have a drive letter, and are over 200 characters could be found with


If you do need to check for multiple criteria, then both checks must be the same size:


will give an error

share|improve this answer
Nice! I like the -- - that's new for me. Better than my answer since it doesn't need array formulae. Presumably faster too, especially for whole columns. – benshepherd Sep 18 '13 at 21:57

=SUM(IF(LEN($A:$A)>200,1,0)) Ctrl + Shft + Enter should do the trick.

If not, =SUMPRODUCT(--(LEN($A:$A)>200)) will work as well.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .