I have been trying to figure out how to make the following sumif formula (if sumif is can even be used in this circumstance) to work all day...

In the tab I want to do the sum if formula in, the MRNs are in one column, comprising all of the rows (lets say A1 is the header MRN, A2 - A100 are all mutually exclusive MRNs - the MRNs are in TEXT format).

Cell B1 is the header CPT1 (CPTs are 5 digit hospital billing codes), Cell C1 is the header CPT2, Cell D1 is the header CPT3, Cell E1 is the header CPT4.

In cells B2-E2 I want to do a sum if formula on the MRN and CPT1, CPT2, CPT3 and CPT4.



On a SEPARATE tab, I have the data I want to do the look-up on. Column 1 (of this separate tab) holds the MRNs (in text format), Column 2 holds the CPTs (in text format), and Column 3 holds the Units (in number format) associated with the CPTs. There are more (many more) CPTs than CPT1, CPT2, CPT3 and CPT4.



I know how to do a sumif formula in cell B2 of the working tab to look up the total units in column 3 of the tab containing the data (=sumif(DataTabMRNs,CellB1,DataTabUnits).

I don't know how to do the look up on the MRN in cell B2 AND the CPT I want to call out (I was going to call out the CPT by somehow referencing the datatab column CPTs = "CPT1" (again, the CPTs are in text form).


I'm not sure if anyone will be able to actually understand the above, but I thought I would try. I know that I will use this again. I also tried using an array (control shift enter) formula and incorporating sumif(and...) based on suggestions I read, but I still couldn't get it to work.

Thank you for any help!