Extend SUM, MIN, and MAX formulae
AccountingWEB.com contributor David H. Ringstrom CPA recently published a collection of useful tips on how popular formulae can be extended by using multiple criteria. Here are the highlights.
By now most accountants probably know their way around Excel’s SUM, MIN, and MAX functions in Excel. Typically, each of these functions is used to derive a result from a range of cells, for example:
- =SUM(C2:C6) would add up all values in cells C2 through C6
- =MIN(C2:C6) would return the smallest value within cells C2 through C6
- =MAX(C2:C6) would return the largest value within cells C2 through C6
But there are times when these kinds of calcuations are not enough. In some instances you may want to add some extra criteria. It’s simple to do, but you need to add a special keystroke after you type the formula.
You’re probably used to pressing Enter once you’ve typed a formula in a cell. To add more criteria to a formula, you’ll need to press Ctrl-Shift-Enter after you type the formula. If you accidentally push the Enter key and Excel accepts a shorter version of the formula you want, press F2 to edit it, and then press Ctrl-Shift-Enter.
"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for AccountingWEB partner CPE Link and contributes articles on Excel to Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com.