- To post as a guest, your comment is unpublished.· 1 years agoI 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?
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.|
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agoHi there - I followed your process exactly but it didn't work? Can you please assist?