Sum of specific data in separate Excel worksheets

Question

I have the following data on two separate sheets:
Sheet1

ABCD a ff dd ff ee b 12 10 10 12 

Sheet2:

  ABCD a ge ff ff ee b 11 13 14 10 

Now I want to write a formula for summing all the values ​​in line 2 that contain ff immediately above (i.e., in line 1)

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

Answer 1

For two sheets, I would go with the brettdj suggestion, but, in general, you can execute SUMIF on multiple sheets, for example:

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

where the list of sheets is a named range containing all sheet names

Answer 2

This is a typical SUMIF . Since SUMIF not originally a three-dimensional function that works on multiple sheets, you will need a formula like this (introduced in Sheet1) that combines the totals from both sheets

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

Please note that this question would be better asked for Superuser, as it does not program