Forum Discussion
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))
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))
Thank you for your reply.
Is there a way to have Peter and Evan and possibly more names on different sheets but same cell?
You can do this, but you must build two independent SUMIFS, and then sum their results as Willy explained in the previous reply!
- 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.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))