Learn Excel
EN
Sets how numbers, dates and formulas look, so everything works just like your own Excel.

Home › Learn Excel › Lesson 6

SUMPRODUCT, SUBTOTAL and MOD in Excel

Multiply and add in one go, total only what's visible, find remainders and measure spread.

  1. Multiply and add: SUMPRODUCT. 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 D2
  2. Totals that ignore hidden rows: SUBTOTAL. SUBTOTAL 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 B7
  3. What's the remainder?.
  4. Remainders: MOD. MOD 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 C2
  5. Your turn: STDEV. The standard deviation shows how far values are spread from the average. STDEV.P uses the whole group. Work out the standard deviation of the scores, in B10

← All lessons