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

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?

-

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

-
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

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

-