.JPG){kind=small}
Master Excel formulas and shortcuts with this comprehensive guide on managing worksheets. Learn different methods to calculate totals, including the use of SUMIF and SUMPRODUCT functions, and discover the most efficient way to avoid errors and long formulas.
Key Insights
- Basic Excel functions such as SUM can be used to calculate totals in a list, but this method can be prone to errors and tedious if the list is long.
- The Alt/= shortcut in Excel can automatically find all other SUM formulas and calculate the total, making it less likely to produce errors.
- SUMIF is a built-in Excel function that calculates the sum of the values in a range that meet a specified criterion. However, it can lead to errors if the criterion is misspelled or incorrectly formatted.
- SUMPRODUCT is another Excel function that multiplies ranges or arrays together and returns the sum of the products. It requires numeric arrays and can be used with the N function to convert TRUE/FALSE values to 1/0.
- The simplest and most efficient method to calculate totals in a list, including subtotals, is to sum all the values and divide by 2. This works because each subtotal is a sum of individual values, effectively counting each value twice.
- This method is also applicable to horizontal ranges, making it a versatile solution for different worksheet setups.
Build a Dashboard
-
Source Sheet
Raw data lives on its own tab — never touched after import.
-
Pivot Sheet
Pivot tables that summarize the source data.
-
Dashboard Tab
Charts, slicers, and KPIs that draw from the pivots.
-
Refresh on Open
Right-click pivot → PivotTable Options → Refresh on open.
Suppose you have a worksheet which looks something like this:
Clearly, what’s needed is the grand total in cell B38. But what is the formula which goes there? Notice that there are several subtotals in the list. The formula in cell B9, for example, is =SUM(B4:B8). How many of you would use the formula =B3+B9+B14+B20+B25+B32+B37 to solve the problem?
I’ve seen formulas like this many times, and often a lot longer because the list is longer. It does work, producing the correct answer, but it’s tedious to do and certainly subject to errors because you might click the wrong cell.
In this article I’ll describe a few alternative ways to do this, leaving the best for last!
The first way is probably the easiest, but the formula is perhaps just as long. While the cell B38 is selected, press ALT/= (that is, hold the ALT key down and press the = sign). This creates this formula:
Wow! That’s cool! Excel “finds” all the other Sum formulas and picks out the correct cells! Not likely to produce an error! But there’s no hunting for the correct cells to use. One down side to this technique is that it won’t work on a range which is horizontal, only vertical, as this one is.
Let’s look at some shorter ones:
This doesn’t look much shorter, but it’s pretty much the same size no matter how many cells are involved. So it’s definitely shorter than something like =B1+B6+B11+B17+B20+B26+B32+B37+B40+B44+B50+B54+B60+B66+B81+B90+B99+B106+B112+B121+B126+B130+B134+B154+B159+B144+B176+B188+B192+B198+B206!
And I’ve seen longer! (4 lines’ worth!)
OK, what is SUMIF? It’s a built-in Excel function which works like this: Wherever the first range (A1:A37) contains the word “Subtotal”, add the values from the 2nd range (the 3rd parameter). The actual syntax is:
That’s pretty cool too! But this could also lead to an error if somewhere in column A the word Subtotal were misspelled, or contained a space at the end (“Subtotal ”)!
There’s also SUMPRODUCT:
Each array must be a numeric array. The solution in this case is =SUMPRODUCT(N(A1:A37=”Subtotal”), B1:B37).
The N function changes TRUE to 1 and FALSE to 0. Without it, the array would be full of TRUE/FALSE values and that’s not numeric, and it would give a 0 for an answer!
OK, ready for the winner? Make sure you’re sitting down first.
How about =SUM(B1:B37)/2:
What? How can that work?
Well, let’s look at the formula in B37, for example:
That’s adding up the values in B33:B36. Those values total 2,131. If I add the values in B33 thru B37 (including the subtotal), I get 4,262 – the 2,131 from B33:B36 and the same 2,131 from B37. So I’m adding the values in twice! If I divide by 2, I get the right answer! Each of the sections containing the subtotal had the values in there twice – the individual values plus the subtotal! So dividing by 2 solves the problem!
This makes the technique work horizontally as well, using a formula something like =SUM(A2:AK2)/2.
Here’s the same data we were looking at, but turned on its side:
Clearly, we get the same answer!
No more lonnnngggggg formulas to do this task!
.JPG)
