Exceljet

Quick, clean, and to the point

This video is part of our online video training.

How to use SUMIFS with a table

Tags 
Summary 
In the video, we build one set of SUMIFS formulas with a table, and one set without. This makes it really easy to see the nice benefits you get when you combine tables and formulas.
Video Transcript 

In this video, we'll look at how to use the SUMIFS function with an Excel table.

On this worksheet, I have two identical sets of order data. I'm going to walk through the process of constructing a summary of sales by item for both sets of data.

With the data on the left, I'll use standard formulas and no table.

On the right, I'll create an Excel Table, and build formulas that use structured references.

To start off, I'll add a bit of formatting to the data on the left to make it easier to see.

Now I'll use the SUMIFS function to sum sales by item.

The sum range comes from data in column F. I need to lock this reference so it won't change when I copy it down.

The criteria range comes from column D, and also needs to be locked. The criteria itself comes from the adjacent column.

=SUMIFS($F$5:$F$19,$D$5:$D$19,H5)

When I copy the formula down, we get a total for each item.

This is a fairly typical SUMIFS solution.

Now I'll build the same solution using a table.

First, I'll create the table

Then I'll name the table "Orders".

Notice with a table, we get the formatting for free.

Now I'll write the SUMIFS formulas with the table.

As before, the sum range is the Total column, and the Criteria range is based on the Item column.

Now that we're using a table, we'll see structured references instead of regular cell ranges.

These are definitely easier to read.

And, as a bonus, there's no need to lock these ranges -- they automatically function as absolute references.

The criteria itself comes from the adjacent column, exactly the same as before.

=SUMIFS(Orders[Total],Orders[Item],Q5)

When I copy the formula down, we get identical totals.

The SUMIFS formulas on the left use regular ranges.

The formulas on the right use the Orders table.

The table-based formulas aren't any harder to write, and they're definitely easier to read.

However, they key advantage shows up when I paste more data below each table.

Because table ranges automatically expand, the formulas on the right automatically update to show correct values.

In the SUMIFS formulas on the left however, the ranges are static and don't change automatically.

I will need to update ranges manually, whenever new data is added.

I'll also need to apply formatting.

As you can see, using a table with SUMIFS is a straightforward process, and provides a number of nice benefits.

Author 
Dave Bruns