Counting rows efficiently

[nomnomgirl]

Hi everyone. So I'm new here, and hope someone can help out

Background info:
I am extracting data from an electronic medical record (EMR) which has many search limitations. I am able to export data into a CSV file (attached) to perform some calculations (All the patient data is fake btw). I am trying to find the number of appointments for each patient within a specific date parameter. For each patient, one appointment appears on one row.

pt appt sample.png

Problem:
What I want to do is count the number of rows (appointments) for each patient in an efficient manner. Right now I've just used the COUNTA function, but don't want to repeat this for every patient, due to the way that the workbook is set up. The data is all from the CSV file from the EMR; the only column I've added is the "Appointments since 2013" column.

The patient data is fake because I am using a test server for the EMR. However out in a real doctor's office, there will be over 2000 patients with possibly up to about 100+ appointments.

Not sure if there's formula out there that would make my task a lot easier and more efficient, or I need to play around with the columns to set it up differently. Any help is appreciated.

EDIT: xls workbook is now attached.

[mikeTRON]

Welcome to the forum.

Can you post a sample excel workbook with the data already imported and laid out in the manner in which you want to proceed? Also you have a column stating # of Apointments then appts since 2013, How are those different? All in be more specific about what you are asking for and I think we can help.

[popipipo]

Is this the result you want?

[nomnomgirl]

Welcome to the forum.

Can you post a sample excel workbook with the data already imported and laid out in the manner in which you want to proceed? Also you have a column stating # of Apointments then appts since 2013, How are those different? All in be more specific about what you are asking for and I think we can help.

Ah,sorry, I should have posted the xls file in the first place. I've attached it now.

The column for # of appointments is the total number of appointments a patient has had ever (i.e. including those before 2013). I am only interested in finding the appointments since 2013 because the EMR's report generator will not produce this number in any of the output columns. I have to count the number of appointments that fall within a specific date parameter, where one appointment is represented by a row underneath the patient number.

Is this the result you want?

This definitely looks promising. Would I have to type in this formula for each patient,though? Also, can you explain the formula?

Thanks for your help so far.

[popipipo]

=IF(ISNUMBER(A2);COUNTIF(A:A;"*"&(B2)&"*"&C2&"*");"")
If A2 don't contain a number you don't get a result.
Counting the Name (eg Bruce Wayne incl. wildcards) in range A:A

[nomnomgirl]

=IF(ISNUMBER(A2);COUNTIF(A:A;"*"&(B2)&"*"&C2&"*");"")
If A2 don't contain a number you don't get a result.
Counting the Name (eg Bruce Wayne incl. wildcards) in range A:A

Alright, thanks for clarifying the formula.

Counting the patient name including wildcards worked in most cases, but there are instances where the row did not contain the patient's name, only the details of the appointment. This made the count inaccurate - see attached image. For example, rows A35 (Louis Audet) and A38 (Jessica Beaudry). This may happen with real data from a doctor's office.

What would I do in this case?

[popipipo]

I don't have an answer for that.