Quick, clean, and to the point

This video comes from our online video training for Excel.

How to build a simple summary table

Although Pivot Tables are fantastic tools for summarizing data, sometimes you just want a simple, lightweight solution based on formulas. The good news is you can build your own summaries using functions like COUNTIF and SUMIF. See how in this 3 minute video.
Video Transcript 

In this video, I want to show you how to build a quick summary table using the COUNTIF and SUMIF functions.

Here we have a sample set of data that shows t-shirt sales.

You can see we have columns for date, item, color, and amount.

So, let's break down this data by color.

Now, before we start, I want to mention that pivot tables would be an excellent way to summarize this data, but you can certainly use formulas for basic summaries, and that's what I'm going to demonstrate here.

First, I'm going to name the color and amount columns in the data. This isn't necessary, but naming these ranges means I won't need to use absolute addresses later, and it'll make the formulas really short and easy to read.

Now, in our summary table, we need a list of unique colors. To build this list, I'll copy the full list, then use the remove duplicates command in Excel.

If you just have a few items in a list, there's no need to use remove duplicates. But it's a great way to build a clean list of unique values when you're working with unfamiliar data.

Now I'll add the first formula. COUNTIF needs a range and a criteria. Since we're counting colors, the range is the color column.

Next, we need a criteria, and that's where our list of colors comes in. We already have the colors in a list in our table, so I can just point to that column and pick up the reference. When I hit enter, and copy the formula down, and we get a count for each color.

Now let's extend this summary to include amounts.

In this case, we'll need to use the SUMIF function. As before, I need provide color as the range, then pick up the name of the color as a cell reference in our table.

Then we need to provide range to sum, which is the Amounts column. When I copy the formula down the table, we have a breakdown of amount by color.

Now, to finish things off, I can copy formatting from the source table to the summary table using Paste Special.

So there you have it, we have our summary table.

If I change some of the data, you'll see the summary table update instantly.

Dave Bruns

Related shortcuts