Q. How is the new Excel Sumlfs different from SumIf?
A. SumIfs is configured almost completely in reverse from a SumIf. A SumIf begins with what range to examine, then by what criteria to examine it and concludes with what range to sum (if it’s different from the first range). SumIfs starts with the range to sum and then, in pairs, a range to examine and criteria to examine it by. You can have multiple pairs of these in a SumIfs. A SumIf can only have one.
Here is a sample of each one. The first one creates a sum with the values in B2:B9, but only if the corresponding value in A2:A9 is equal to “Red.” The second one creates a sum with the values in B2:B9 if the values in A2:A9 are “Red” and if the value itself is over 10.
Q. I’m getting unpredictable results in a formula. How do I “dissect” a formula to troubleshoot?
A. Several great tools are available in Excel to reverse engineer formulas. Some can be found in the Formulas tab in the Formula Auditing group. Let’s look at Trace Precedents.
Clicking Trace Precedents while in a cell with a formula will draw arrows that point to cells being used, if they are on the same worksheet. If they are on a different worksheet in a different workbook, Trace Precedents will draw a dashed arrow pointing to an icon, indicating cell references that are not on this worksheet. If you double-click the arrowhead or the dashed line, it will bring up the Go To dialog box, which lists all the “foreign” cell references. Select the one you want to trace, and click OK to navigate there. Clicking Remove Arrows in the same Formula Auditing group should return it to a normal look.