If then type formula to extract/count items in a column

Good morning all -

I am working on a spreadsheet and have extracted data from an ODBC database and need your assistance with a formula.

I have in column L, certain codes as they apply to public safety responses. Codes such as 5150, 01A01, 17A02, 25A01, 04B01, etc.

I need to count these codes in the following manor: All 5150 & 25A01 & 25A02 together, All remaining codes with 'A', and then all 'B' codes together.

Can you please help and tell me the best way to accomplish this? My knowledge is in Crystal and not so much Excel. Thank you in advance.

Will the Alpha statement above in your second formula exclude the Alpha calls it already counted in the 5150 formula that you posted first?

I don't want it to count the 25A01 & 25A02 again in the 'A' count.

Originally Posted by NBVC

Try:

=SUM(COUNTIF(L:L,{"5150","25A01","25A02"}))

and

=COUNTIF(L:L,"*A*")-SUM(COUNTIF(L:L,{"25A01","25A02"})

=COUNTIF(L:L,"*B*")

Thank you NBVC, that worked perfectly! I appreciate the quick responses.

Originally Posted by NBVC

Have you seen my second formula:

=COUNTIF(L:L,"*A*")-SUM(COUNTIF(L:L,{"25A01","25A02"})

this subtract the counts of those 2 from the other A's

Yes, sorry...you had replied to my OP when I was composing my question to 'jeffreybrown'. Thanks again!

One additional question related to my OP:

In column M I have whether the patient was transported or not (1 for yes 0 for no), what formula could I use in conjunction with what you guys have already provided to count the codes in column L if there is a '1' in column M, indicating a transport?

Thank you!