I have a spreadsheet with a column B containing data like this:
Dark-Shadows-0247.avi
Dark-Shadows-0248.mp4
Dark-Shadows-0249.mp4
Dark-Shadows-0250.mp4
Dark-Shadows-0251.avi
Dark-Shadows-0252.avi
Dark-Shadows-0253.avi
Dark-Shadows-0254.avi
Dark-Shadows-0255.avi
Dark-Shadows-0256.avi
Dark-Shadows-0257.avi
Dark-Shadows-0258.avi
Dark-Shadows-0259.avi
Dark-Shadows-0260k.avi
Dark-Shadows-0261.avi
Dark-Shadows-0262.avi
Dark-Shadows-0263.mp4
Dark-Shadows-0264.avi


I want a count of mp4 files and avi files. So I created a column AA with a formula like =RIGHT(B2,3) which gives me a column containing just "avi" or "mp4". Then at the bottom I can use: =COUNTIF(AA2:AA1226,"mp4") to get the count of mp4 files. There is other stuff encoded in my filenames. That 260k.avi is a kinescope. And column AB has formulas like: =LEFT(RIGHT(B2,5),2) which in the case of a kinescope results in "k." in the column. And then at the bottom, =COUNTIF(AB2:AB1226,"k.") gives me a count of kinescopes. This all works great. I don't even need to hide columns AA, AB, ... since they are so far over. Still, just for the elegance of it, I would like to eliminate these extra columns and use count formulas that work directly on column B. I have tried a number of formulas but none of them work. Is there some easy way to this this?

I am using Microsoft Office Home and Student 2010 and Excel is version 14.0.7163.5000 (32-bit).

Finally, note that I have a working solution for everything I need and a heroic substitute like resorting to Visual Basic would not very attractive. At some point, elegance loses out to ease of maintenance. Thanks for any help you can suggest.