sumif multiple sheets

[fuzzymonkey]

I have a large number of worksheets in a workbook (but to keep this simple say I have sheet1, sheet2 and sheet3)

In one column (say column A) I have a product code (e.g. A123), and in column B I have the number of items. On all of my worksheets the data is in the first 1000 rows.

I am trying to do something like a sumif to sum all of the number of items across all of the worksheets for a specific product code (i.e. I need to know the total number of product A123 that appears on all the worksheets). SUM allows me to do a 3D referance, but not SUMIF.

 

[Haseeb Avarakkan]

Hello, Try
Enter your all sheet names in a range, let's say C2:C4 on Summary Tab

Summary tab A2 contains the Product Code

B2, copy down..

=SUMPRODUCT(SUMIF(INDIRECT("'"&$C$2:$C$4&"'!A:A"),A2,INDIRECT("'"&$C$2:$C$4&"'!B:B")))

 

[fuzzymonkey]

Helps a lot - exactly what I was looking for. Thanks!