Ad Clicks :Ad Views :
    Excel formulas and tutorials

    Excel formulas and useful tools

    img

    SUMIFS across multiple sheets

    /
    /
    /
    369 Views

    First you have to define a SheetList range name (the range of sheet names being the sources of SUMIFS). You can input the name of the sheets in a separate sheet of your excel file. Select all the cells containing this sheet names, then go to the name box field (the upper left field where you see the address of the cells usually – as seen in the picture) and write SheetList.

     

    After this just use the following formula, where INDIRECT(“‘”&SheetList&”‘!$E:$E”) is the amounts column and INDIRECT(“‘”&SheetList&”‘!$F:$F”) is the criteria column.

    Don’t forget the SUMPRODUCT, it doesn’t work without it.

    =SUMPRODUCT(SUMIFS(INDIRECT(“‘”&SheetList&”‘!$E:$E”),INDIRECT(“‘”&SheetList&”‘!$F:$F”),A5)

    Have fun with Excel!

    • Facebook
    • Twitter
    • Google+
    • Linkedin
    • Pinterest

    Leave a Comment

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    This div height required for enabling the sticky sidebar