Forum Discussion
SUMIFS for criteria across multiple sheets
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.
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))
6 Replies
=SUMIFS(C:C,A:A,E1,B:B,'sheet1'!$A$1)+SUMIFS(C:C,A:A,E1,B:B,'sheet2'!$A$1)
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!
