SUMIFS for criteria across multiple sheets

In this case, the best technique is to make a list of these names on the main sheet, so that they are linked to names in the other sheets and they change by changing that names.

To quickly make this list, let's suppose that the sheets in your workbook are named in this way:

Sheet1, Sheet2, and so on, and the names all are in cell A1 of each sheet.

Then easily you can use the below formula and drag it down to make links to all names on the mainsheet:

=INDIRECT("Sheet"&ROW(A1)&"!A1")

After that, you can use just one SUMIFS with the support of SUMPRODUCT to calculate the value as follows:

=SUMPRODUCT(SUMIFS(C:C,A:A,E1,B:B,$A$9:$A$13))