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 this data down 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. Now, this isn’t necessary, but naming these ranges means I won’t need to use absolute addresses later, and it will 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, we have a count for each color. Now let’s extend this summary table to include amounts. In this case, we’ll need to use the SUMIF function. As before, I need to 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 the 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. And, if I change some of the source data, you’ll see the summary table update instantly.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
