Sum of specific data in separate Excel sheets

7
votes

I have the following data in 2 separate sheets:
Sheet1:

    A   B   C   D
a   ff  dd  ff  ee
b   12  10  10  12

Sheet2:

    A   B   C   D
a   ge  ff  ff  ee
b   11  13  14  10

Now I want to write a formula to sum all the values in row 2 which contain ff directly above (i.e. in row 1)

In my example above I want to add (Cell Sheet1[A, b], Sheet1[C, b], Sheet2[B, b], Sheet2[C, b]) which is equal to 49.

2
excelexcel-formula

2 Answers

11
votes

For 2 sheets I'd go with brettdj's suggestion, but, generically, you can perform SUMIF across multiple sheets like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!A1:D1"),"ff",INDIRECT("'"&sheetlist&"'!A2:D2")))

where sheetlist is a named range containing all the sheet names

10
votes

This is a typical job for SUMIF. As SUMIF isn't natively a 3D function that works accross multiple sheets, you will need a formula such as this one (entered on Sheet1) combining the totals from both sheets

=SUMIF(A1:D1,"ff",A2:D2)+SUMIF(Sheet2!A1:D1,"ff",Sheet2!A2:D2)

Note this question would have been better asked on Super User as it is not programming