Tried SumIF and Index/match with dates -- no luck

[Cheddabeast]

Been working on this off and on for the last couple days and running out of ideas. A little context -- I run a small physical therapy business and have two reports, one that provides reimbursement for each medical claim (Receipts Example). The other is line item of charges including dates of service (Charges Example). I am trying to get down to average reimbursement per visit; however, the a patient can have 2 or 3 visits per week and the Receipts Example report only provides a date range, but not how many visits fall within that range.

For examples on the Receipts Example spreadsheet, Row 2 is one payment for account number 347202 for all their visits ranging between 10/01/2021 and 10/12/2021, but I have no idea if that payment contains 1, 2, 3, or 4 visits. That is where the Charges Example spreadsheet comes into play -- it does list each line item that is billed. I added the visit and total per visit columns and used the IF/And and SumIFs functions to combine each line into "visits". Rows 2 thru 5 all have the same "Service Date" in column D for account 347202. Rows 6 thru 9 are for the same account, but have a service date of 10/12/2021 and finally 10 thru 13 a different date of service, 10/26/2021.

To summarize account 347202 had 3 total visits, 10/5/2021, 10/12/2021, and 10/26/2021 according to the Charges Examples spreadsheet. The 2 payments for account 347202, reflected on Receipts Example spreadsheet, are broken down by all visits between 10/01/2021 (column C2) - 10/12/2021 (column D2) and 10/13/2021 (column C3) - 10/30/2021 (column D3). So I know he had 2 visits for row 2 and 1 visit for row 3. I'd like to have column O of "Receipts Example" reflect how many visits are contained within that payment.

I've tried using IF & And functions but its difficult with dates. I wish I could use a v-lookup but so many duplicate account numbers. Any suggestions or ideas how I could accomplish this? I've re-read what I typed and I even get a bit confused myself so I am sure there will be questions. I am also sure their is an easier way to accomplish this and I am making it more complex than it needs to be.

I am using Excel 2016. All patient information is retracted.

[Richard Buttrey]

Do you happen to have Excel 365?

[Cheddabeast]

No, unfortunately I use Excel 2016. Definitely need to make the upgrade.

[JohnTopley]

Try

in O2

=SUMPRODUCT(--('[Charges Example.xlsx]Sheet1'!$G$2:$G$100=B2)*('[Charges Example.xlsx]Sheet1'!$D$2:$D$100>=C2)*('[Charges Example.xlsx]Sheet1'!$D$2:$D$100<=D2),('[Charges Example.xlsx]Sheet1'!$E$2:$E$100))