Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to sumif with 3d reference to sum across tabs based on criteria in Excel?

In last article, I have talked about how to create 3D refences to sum same cell across sheets. In this article, I will introduce the method on sumif with 3D reference to sum across sheet tabs based on criteria in Excel as below screenshot shown.
doc 3d sum by criteria 1

Sumif 3D reference for multiple criteria

Sumif 3D reference for multiple criteria

To solve this job, you need do as follows:

1. List the sheet names you want to use to sum in a sheet, then give the list a name by going to the Name box, and type name then press Enter key.
doc 3d sum by criteria 2

2. Then, in a cell which you will place the result at, type this formula =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"A2:A4"),A2,INDIRECT("'"&Sheets&"'!"&"B2:B4"))), press Enter key, and drag fill handle over cells.
doc 3d sum by criteria 3

In the formula, A2 is the specified criteria you will sum by, A2:A4 is the same criteria range in each sheet you will sum by, B2:B4 is the same range in each sheet bayou will sum, Sheets is the named range you specified in step 1.

Sum absolute values

If there are some values which include both positive and negative values, how can you sum their absolute values up?  Generally, you may change the negative values to positive first, and them sum them. But with Kutools for Excel's Sum absolute values, you can directly sum the absolute values as quickly as you can. 
doc sum absolute

Excel Productivity Tools

Kutools for Excel - The Best Office Productivity Tool

  • Brings 300 powerful advanced features to Excel, enable you to do smarter, faster and better.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Office Tab - brings tabbed interface to Office, make your work much easier.

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jon · 1 years ago
    Hi there - I followed your process exactly but it didn't work? Can you please assist?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      I have checked the formula before i posted, so i do not know why you cannot get the right answer. Have you checked if your formula refernces and sign are correct?