Ad Clicks : Ad Views :
Excel formulas and tutorials

Excel formulas and useful tools

img

SUMIFS across multiple sheets

/
/
/
250 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