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