Need help on a sumif formula using multiple sheets

[lundzy]

I need a formula to compare column c in 3 different sheets and then if they equal a specific date in column c then add the values which are located in column d? Can I do this with one formula? I was able to get this done on one sheet but adding multiple sheets has got me stumped. Here is what I have so far =SUMIF(('Barn 1'!C11:C53,'Barn 2'!C11:C53,'Barn 3'!C11:C53),('Barn 3'!C11),('Barn 1'!D11:D53,'Barn 2'!D11:D53,'Barn 3'!D11:D53))

[#2]

@lundzy 

Hi,

If you first go to Name Manager (Formulas tab) and make the following definition:

Name: SheetList

Refers to: ={"Barn 1","Barn 2","Barn 3"}

(Or whatever happen to be the sheet names in question.)

You can then use:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C:C"),'Barn 3'!C11,INDIRECT("'"&SheetList&"'!D:D")))

Cheers

[#3]

Thank you so much,  a bit over my head but worked like a charm so really appreciate the quick response.

[#4]

@lundzy 

You're welcome.

FWIW, if you're uncomfortable with the complex nature of the formula then, assuming you'll only ever have 3 sheets, you could always write 3 separate SUMIF formulas - one for each sheet - and then sum these results.

The formula I gave you really comes into its own when the number of sheets is quite high, and so summing multiple separate SUMIF formulas becomes a touch unwieldy.

Your call.

Cheers