Can I use an IF statement as a parameter in a COUNTIF function?

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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