Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

Consider:

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".

Consider:

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

3 Answers 3

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:

=SUM(IF(LEN(A1:A10000)>200,1,0))

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:

=SUMPRODUCT(--(LEN(A1:A10000)>200))

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

=SUMPRODUCT(--(LEN(A1:A10000)>200),--(MID(A1:A10000,2,1)=":"))

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

=SUMPRODUCT(--(LEN(A1:A10000)>200),--(MID(A1:A7777,2,1)=":"))

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.