Forum Discussion

lundzy's avatar
lundzy
Copper Contributor
May 21, 2020

Need help on a sumif formula using multiple sheets

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))

Formulas and Functions

3 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Iron Contributor
    May 21, 2020

    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

    • lundzy's avatar
      lundzy
      Copper Contributor
      May 21, 2020

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

      • Jos_Woolley's avatar
        Jos_Woolley
        Iron Contributor
        May 21, 2020

        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

Resources