SUM formula across multiple worksheets

[JDeMaro22]

Hi,

I am looking for a more convenient and risk free formula that will sum all of a doctors patient counts through multiple worksheets in the same workbook. As shown in my screenshot the "YTD" tab is currently just using the cell number as a reference for each doctor which can cause problems if a doctor is removed, moved or added. I am looking for a better formula to sum all of their results using the "NPI" unique identifier in column E.

Thank you,

Josh

[HansV]

One way to do this is to create a list of the worksheet names involved in a range of cells. Name this range - for example - Sheets.

S1107.png

You can then use the following formula in G11:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!E11:E200"),E11,INDIRECT("'"&Sheets&"'!G11:G200")))

Adjust the ranges if needed. Then fill down.

[JDeMaro22]

Hi Hans,

How do I create this range exactly? Do I just place this at the bottom of the YTD sheet?

[HansV]

Wherever you like - somewhere out of the way on the YTD sheet, or on a separate sheet.
By naming the range, you don't have to worry in the formula where exactly it is located.

[JDeMaro22]

I created the range and inserted the formula but I am getting a REF error?

[HansV]

I'd have to see a small sample workbook demonstrating the error (it doesn't have to be your full workbook!)

[JDeMaro22]

Think I fixed it, thanks again for your help Hans I appreciate it greatly.

Josh