Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: 3D SUMIF

3D SUMIF for multiple worksheets in Excel

This tutorial shows how to 3D SUMIF for multiple worksheets in Excel using the example below; Formula =SUMPRODUCT(SUMIF(INDIRECT (“‘”&sheets&”‘!”&”range”),criteria, INDIRECT(“‘”&sheets&”‘!”&”sumrange”))) Explanation To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can do so with the SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the example, the formula looks like this: =SUMPRODUCT(SUMIF(INDIRECT (“‘”&sheets&”‘!”&”D4:D5”),B9, INDIRECT(“‘”&sheets&”‘!”&”E4:E5”))) The…