Summarize tables with SUMIF, one of the most loved Excel Functions.
Imagine you have a big list, maybe thousands of registers. You want to know:
- The sales each vendor did
- The total length of a type of pipe
- The sales by season
- The debt each client has
- And more…
The table shown in graphic 1 is a simplification for the purpose of this explanation; it is a plain list of [Salesman] with the corresponding [Department] and the [Amount].
You want to know…
How much did each Salesman sell?
In other words; you want to turn this table…
Into this other one…
No Pivot Tables this time.
It is time to explore this Excel Function:
Use SUMIF to Sum Values that Match a Single Criterion
Imagine you have a pipe list (graphic 3). Your goal is to ask three different suppliers for quotation.
This list is short for the purpose of explanation; you may have thousands of entries.
You want to summarize the total length of pipes by [Diameter] and by [Material]; after that, you will send a friendly email to suppliers with a summary like the one below (graphic 4):
Tip 1: Create a list with unique entries by deleting duplicates. How? Use Advanced Filter in Excel 2003 or a Table in Excel 2007.
You are ready to summarize the two tables (graphic 4) with Excel SUMIF.
Let’s start; look at the table “Summary by Diameter” below (graphic 5).
You want to sum the length if the diameter is 200 mm.
Look at B17 cell. This Function sums the range [Length] if its corresponding range [Diameter] matches with 200.
In Excel language it sums the range “sum_range” if its corresponding range “range” matches with “criteria”
Then copy and paste the formula.
Tip 2: Copy and paste SUMIF formula with confidence. How? Use always Absolute Reference for the ranges “range” and “sum_range” arguments. Why? You usually copy and paste this formula
Make the formula more readable and consistent with Names. Download this example with range names
Tip 3: Use range names. Why? The function becomes more readable and copying and pasting turns more intuitive.
Look at the table “Summary by Material” below (graphic 7).
You want to sum the length if the [Material] is PVC. How?
Now look at B27 cell.
Finally, copy and paste the formula downward. The summary is ready.
Send the email to suppliers with Carbon Copy (Cc:) to your impatient boss.
Take care that totals are consistent; they must be all 10,836.51 for this example.
Tip 4: Always cross check the total.