Improving accuracy in Excel spreadsheets over time becomes difficult as formulas tend to get longer and less accurate. Often times when we use Excel formulas, we do them as a one-time thing to solve the immediate need. You may copy and paste that same formula somewhere else and the inaccuracies continue. This quick tip for using SUMIF formulas will make your accounting spreadsheets easier to read, quicker to update, and more accurate.
Improving Accuracy in Excel Spreadsheets with the SUMIF Formula
Here’s an example of a spreadsheet for a business that is tracking multiple store locations.
If you’re looking for your in-store total across all columns, does your formula look like this? =B8+D8+F8+H8+J8+L8
Ugh, why? I mean, it started innocently enough. There were just a couple of additional store locations that needed to be added in. No big deal. But then we added more and more to our business and slowly it turned into this monster that you just can’t look away from.
If you’re adding or subtracting across multiple columns, there is a much easier way to handle the setup so that it doesn’t get out of hand as more items are tracked in your reports.
(Similar: Hiring Internal Accounting Staff: Do you know what skillset you need?)
How to Write and Implement a SUMIF Formula
Here’s what you want it to look like in the editing field: =SUMIF(range, criteria, sum_range)
Range: these are the cells where you want the formula to check. Usually, they will be your headings, but there are other possible applications.
Criteria: is what you’re looking for in the range. So, if you want to add together all of the in-store columns, then your criteria would be “In-store”. Alternatively, your criteria could also point to the value of a cell.
Sum_range: is the range of cells where the data that you want to add or subtract is located.
Why the SUMIF Function Works for Improving Accuracy in Excel Spreadsheets
So, a typical SUMIF formula on our multiple store example might look like:
=SUMIF($B$7:$N$7,“In-store”,B8:N8)
And for the second column:
=SUMIF($B$4:$N$7,“Delivery”,B5:N5)
When you create a formula in Excel and want to reuse it somewhere else, Excel assumes that you don’t want to use the actual data from the original formula and that you’re copying the cell locations instead. If you actually do want to continue to use the numbers from the original locations, simply insert dollar sign around that part of the equation.
In our example, we are using the dollar signs to ensure that we are always referencing the headings. We make the references to the criteria “absolute” with the dollar sign because we don’t want that part of the formula to change when we copy it down.
Updating SUMIF in Excel Quickly and Accurately
Updating this formula for newly added columns is a snap. If the columns are added in the middle of the spreadsheet, no problem! The formula already includes these columns. If columns are added later, you may want to double-check that your SUMIF functions include these new columns.
This one-time change in your formulas will alleviate the need for changing multiple long formulas whenever you add to your reports. Instead, you only need to edit the new, shorter formula when you add a new column, or per our example, a new store.
By Emily Weinberger, Accountant at WHH
Emily is a talented researcher who is quickly becoming an expert in various areas of taxation, financial statements and consulting. With a diverse background as a paralegal, notary public and involvement in a family agricultural firm, Emily is an in-demand resource at WHH.
