SUMIFS for criteria across multiple sheets

[Queenie Lai]

Here is the formula I'm working on

F1=SUMIFS(C:C,A:A,E1,B:B,'sheet1:sheet2'!A1)

	A	B	C	D	E	F
1	Jan	Mary	100		Jan	90
2	Jun	Susan	300		Feb
3	Jan	Peter	50		Mar
4	May	Richard	80		Apr
5	Jan	Evan	40		May

sheet1!A1=Peter 

sheet2!A1=Evan

I believe the underline part of the formula is not working. Would be great if I could have some help. Thank you.

[#2]

Hi,

You have to put Evan and Peter on the same sheet as below screenshot, then use this formula:

=SUMPRODUCT(SUMIFS(C:C,A:A,E1,B:B,C9:C10))

[#3]

=SUMIFS(C:C,A:A,E1,B:B,'sheet1'!$A$1)+SUMIFS(C:C,A:A,E1,B:B,'sheet2'!$A$1)

[#4]

Thank you for your reply.

Is there a way to have Peter and Evan and possibly more names on different sheets but same cell? 

[#5]

You can do this, but you must build two independent SUMIFS, and then sum their results as Willy explained in the previous reply!

[#6]

Thanks.
The reason I do not want to build it with multiple SUMIFS is that there are more than 50 names and their names are changed at times.
I would like to build the formula so that when I change the names on the other sheets I do not need to makes changes to the SUMIFS formula.

[#7 · Accepted solution]

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))