How to sum figures across multiple worksheets

[Elizabeth_Otike]

I have 9 worksheets. Each has a column for amount and a column for product category. There are 3 product category.
I want to sum the total amounts by their product category across multiple work sheet. Previously, when it was a single sheet I used this formula- sumifs(sheet1!B:B,sheet1!C:C,”ProductA”)

How do I replicate this across multiple work sheets?

Thanks.

 

[mohadin]

Hi
And welcome to here
Please try

=SUMIFS(Sheet1!B:B,Sheet1!C:C,"ProductA")+SUMIFS(Sheet2!B:B,Sheet2!C:C,"ProductA")+SUMIFS(Sheet3!B:B,Sheet3!C:C,"ProductA")
Or

=SUMIF(Sheet1!C:C,"ProductA",Sheet1!B:B)+SUMIF(Sheet2!C:C,"ProductA",Sheet2!B:B)+SUMIF(Sheet3!C:C,"ProductA",Sheet3!B:B)

 

[Elizabeth_Otike]

Thanks Mohadin. But it’s giving me error, I did it across the 10 worksheet, is there a way I can use “Sum Product”?

 

[mohadin]

I think this helps

SUMPRODUCT for multiple sheets

Hi, I have created a sumproduct that works for a single sheet: =SUMPRODUCT(--(A4:A30="Clouds EASY"), C4:C30) However, I have 40 plus sheets that I need to apply this function to (and over 200 effects). Currently the formula for three sheets (760, 236 and 980) is...

www.mrexcel.com