Summarizing data with Specific/Representative Data Table Example

[Buzz1126]

I need help calculating the number of patients, visits, and gross charges from a data table. Essentially summarizing my data with specific parameters.

A quick rundown of what I need and how my example is set up:
- In the data table there is a column for Patient Name, Insurance, Date of Service, and Cost.
- I need to calculate the number of patients have each insurance (Column C of the Solution)
- I need to calculate the number of visits for each insurance (Column D of the Solution). The number of visits is calculated by looking at the 'date of service' and the 'patient name'. For example, Matt and Snow went to the doctor on 7/11/17. Matt had one entry (one charge) and snow had two entries (two separate charges) for a total of 3 entries. However, there would be 2 patients and 2 visits for that day.
- I need to calculate the gross charge for each insurance (Column E of the Solution).

I spent a good amount of time making sure the data on my example is a representation of my real data. The proposed solution is inputted and calculated manually to show you the correct answers.

Thanks in advance,

Matt

[tim201110]

Patients =SUM(--(IFERROR(MATCH($G$3:$G$20,IF($H$3:$H$20=$B3,$G$3:$G$20),),)=ROW($A$1:$A$18))) as array formula
Visits =SUM(--(IFERROR(MATCH($I$3:$I$20,IF($H$3:$H$20=$B3,$I$3:$I$20),),)=ROW($A$1:$A$18))) as array formula
Gross =SUMIF($H$3:$H$20,B3,$J$3:$J$20)

[Buzz1126]

Sorry for the late reply, but I do appreciate the help! Everything works great!

[Buzz1126]

Could you explain to me the row command in your formula? The formula isn't working when i try transferring it to my data. The data is representative, but the real thing has a lot more columns and a lot more rows. It's also on separate tabs