counting when a pivot table won't work

Hello. I'm working in M365. I have a report I need to produce monthly. I'm hoping to be able to swap out this month's data set for the next and not have to re-create the wheel each time.

I need help figuring out how to count values that occur in multiple columns. I'm a big fan of pivot tables, but unfortunately it doesn't seem accurate for this particular data set.

I need to count each procedure, not just number of patients. As you can see there can be multiple procedures per patient. The example surrounded in red shows how one "inpatient" value would actually need to be counted twice. Once for PCI and once for Heart Cath.

One example is, I want to count the number of PCIs done. You can see in the screenshot that the PCI procedure can be listed in any of the procedure columns.

To add one more level of complexity, I need to be able to count inpatient and outpatient procedures separately.

It would be a bonus if we can combine procedures into a single category (e.g. "LEFT HEART CATH" and "LEFT HEART CATH W/GRAFTS" are the same for the purpose of this report).

I was able to use SUM COUNTIFS to get accurate totals. BUT that requires me to swap out the worksheet name in the formula each month. And I'm not sure how to make it work for separating into inpatient and outpatient.

Any help is appreciated. Thank you in advance!

Raw Data

End goal would be to provide counts in this table.