Q. I don’t understand the new SumIfs function. How is it 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. Above 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, but can’t figure out what the problem is. Is there a way to “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. If you have a cell with incorrect results, this can help you trace where it may have come from. When you have your answers, clicking Remove Arrows in the same Formula Auditing group should return it to a normal look.
Q. Reformatting doesn’t seem to fix my date formats. How can I get them consistent?
A. Clean up inconsistent date formats in a column using this two-step process:
- Select the column letter that you need to fix and clear the formatting (Home tab, Editing group, Clear Button).
- Click the Number Format field in the Number group on the Home tab and choose Short or Long Date. You can also choose the Dialog Box Launcher for more date options.