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.
How to build a simple summary table
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.
COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,...
The Excel SUMIF function returns the sum of cells that supplied criteria. Criteria can be applied to dates, numbers, and text using logical operators (>,,=) and wildcards (*,?) for partial matching....
The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,...
SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when adjacent cells meet criteria based on dates, numbers, and text. SUMIFS supports logical operators (>,...