Help with SUMIFS formula across multiple sheets

Hi all,

I am a relatively inexperienced with most Excel formulas beyond the basics, so appreciate your help.

I am trying to work with an existing dataset that has billing info for over a hundred clients, with each client listed on their own sheet. I am trying to create a summary page that lists the payments received per provider, per month. Currently, each sheet has the provider listed in only one place.

Some basics on the dataset:

Tabs: are each named with client initials
Provider: Is listed by name, only once per tab, in the B5 cell of each tab
Date of Payment: is listed in in Column C of each tab
Payment Amount: is listed in Column E of each tab

After reviewing things online it appeared that the best way of doing this was using the SUMIFs function. The formula I copied and modified from online was this (NOTE: Only listing four sheets/clients for illustrative purposes):

=SUMPRODUCT(SUMIFS(INDIRECT("'"&{"AB","GB","RB","VC"}&"'!$E$10:$E$33"),INDIRECT("'"&{"AB","GB","RB","VC"}&"'!$B$5:$B$5"),"SEAN*",INDIRECT("'"&{"AB","GB","RB","VC"}&"'!$C$10:$C$33"),">="&C10,INDIRECT("'"&{"AB","GB","RB","VC"}&"'!$C$10:$C$33"),"<="&EOMONTH(C10,0)))

The first criteria is for the provider name (in this case, "Sean"). The next two criteria are related to the date range. This formula produces a #VALUE! error, which I know has something to do with Criteria 1 (i.e., cell B5).

I can get the formula to work correctly if I list the provider name in each row next to the payment received in column F:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&{"AB","GB","RB","VC"}&"'!$E$10:$E$33"),INDIRECT("'"&{"AB","GB","RB","VC"}&"'!$F$10:$F$33"),"SEAN*",INDIRECT("'"&{"AB","GB","RB","VC"}&"'!$C$10:$C$33"),">="&C10,INDIRECT("'"&{"AB","GB","RB","VC"}&"'!$C$10:$C$33"),"<="&EOMONTH(C10,0)))


However, I am really hoping to not have to add the provider name to each row if avoidable. Is there another way to write the first formula to essentially say, "If Provider X's name appears in cell B5, then sum the totals in Column E"?

Secondly, is there a better way of writing a formula to search and sum across each of the sheets without having to list them all? As noted, there are already over 100 clients/sheets in this dataset with more being added every week. I would like to avoid having to update the formula every time a new sheet is added, but haven't had any luck finding a solution to that. I know that we would probably be better off not using so many different sheets, but our billing person prefers for each client to have their own sheet for her tracking purposes.

I appreciate any help you can provide!

Okay, thanks! That makes sense.