Home › Learn Excel › Lesson 6
Multiply and add in one go, total only what's visible, find remainders and measure spread.
SUMPRODUCT multiplies the ranges row by row and then adds everything up, in one go. Work out the total order value (quantity times price) in D2SUBTOTAL does a calculation picked by a number: 9 means sum. Unlike SUM, it skips rows hidden by a filter. Add up the amounts with SUBTOTAL, in B7MOD returns the remainder after a division: 17 divided by 5 is 3, with something left over. Work out what's left over when you divide 17 by 5, in C2STDEV.P uses the whole group. Work out the standard deviation of the scores, in B10