Countif Function

[day92]

Hi guys I have an issue im stuck on. What im trying to do is count the number of occurrences before and after a certain number. I have a list of data in three columns and I want to count the number of CPT Codes before and after CPT code 59510, 59400 or 59610 for each MRN. In the attached spreadsheet I have some sample data with expected results.

Thanks in advance.

[uncle_romania]

Add a column for cpt to your results table (column F) then you can type this into G3

=INDEX(MATCH(F3,C:C,0),1,1)-2

to give the row count before the instance of any value typed into F3 (excluding column title). Any instance after can be found by the formula

=COUNTA(C:C)-(G3+1)

[day92]

Hi Uncle - thanks for the formula but that does not really solved my issue. It only gives me the count of CPT before a certain number but it does not do it by MRN nor does it give me the count after a certain number. Unless I missed something?

[tigeravatar]

day92,

Attached is a modified version of your example workbook.
I added a helper column in your output table named "cpt"
The helper column is in column F

In cell F3 and copied down is this formula:

Please Login or Register  to view this content.

In cell G3 (Before) and copied down is this formula:

Please Login or Register  to view this content.

In cell H3 (After) and copied down is this formula:

Please Login or Register  to view this content.

Is something like that what you're looking for?

[day92]

TA - you are amazing that is exactly what I was looking for. Thanks a lot man. Ive been racking my brains all morning trying to figure that out.

[tigeravatar]

You're very welcome