A vertical summary is a way of summarizing data from multiple columns into a single column, showing the total count of each unique value. For example, if you have a table of sales data with columns for product, region, and quantity, you can create a vertical summary that shows how many times each product appears in the table.
There are different ways to create a vertical summary in Excel, such as using pivot tables, dynamic arrays, or VBA code. In this article, we will focus on using the COUNTIF function, which is a simple and versatile formula that can count cells that meet a certain criterion.
The COUNTIF function takes two arguments: a range of cells to count, and the criterion to use for counting. For example, to count cells equal to “red” in a range, we could use COUNTIF like this:
=COUNTIF(range,"red")
The criterion can be a value, a cell reference, or an expression. For example, to count cells that are greater than 10, we could use:
=COUNTIF(range,">10")
To create a vertical summary from columns with count, we need to use the COUNTIF function in two steps:
- Get the unique values from the columns that we want to summarize. We can use the UNIQUE function, which returns an array of unique values from a range or an array. For example, to get the unique products from column A, we could use:
=UNIQUE(A:A)
- Count the occurrences of each unique value in the columns that we want to summarize. We can use the COUNTIF function with the spill reference operator (#) to refer to the array returned by the UNIQUE function. For example, to count the occurrences of each product in column A, we could use:
=COUNTIF(A:A,UNIQUE(A:A)#)
Procedures
To create a vertical summary from columns with count using the COUNTIF function, follow these steps:
- Select a cell where you want to start the summary table. For example, cell E1.
- Enter the formula
=UNIQUE(A:A)to get the unique values from column A. Press Enter. The formula will spill into the cells below, showing the unique products. - Select the cell next to the first unique value. For example, cell F2.
- Enter the formula
=COUNTIF(A:A,E2#)to count the occurrences of each product in column A. Press Enter. The formula will spill into the cells below, showing the counts for each product. - Optionally, you can add labels to the summary table, such as “Product” and “Count”, and format the table as desired.
Explanation
The following table shows an example of sales data with columns for product, region, and quantity:
| Product | Region | Quantity |
|---|---|---|
| Apple | North | 10 |
| Banana | South | 15 |
| Cherry | East | 20 |
| Apple | West | 25 |
| Banana | North | 30 |
| Cherry | South | 35 |
| Apple | East | 40 |
| Banana | West | 45 |
| Cherry | North | 50 |
We want to create a vertical summary that shows how many times each product appears in the table. To do this, we can use the COUNTIF function as follows:
- In cell E1, we enter the formula
=UNIQUE(A:A)to get the unique products from column A. The formula returns an array of {“Apple”;“Banana”;“Cherry”}, which spills into the cells E2:E4. - In cell F2, we enter the formula
=COUNTIF(A:A,E2#)to count the occurrences of each product in column A. The formula uses the spill reference operator (#) to refer to the array returned by the UNIQUE function in E2:E4. The formula returns an array of {3;3;3}, which spills into the cells F2:F4. - We add labels to the summary table, such as “Product” and “Count”, and format the table as desired. The final result looks like this:
| Product | Count |
|---|---|
| Apple | 3 |
| Banana | 3 |
| Cherry | 3 |
Scenario
To illustrate the use of the COUNTIF function for creating a vertical summary from columns with count, let’s consider a scenario where we have a table of survey data with columns for name, age, gender, and rating. The table looks like this:
| Name | Age | Gender | Rating |
|---|---|---|---|
| Alice | 25 | F | 4 |
| Bob | 30 | M | 3 |
| Carol | 35 | F | 5 |
| David | 40 | M | 4 |
| Eve | 45 | F | 3 |
| Frank | 50 | M | 5 |
| Grace | 55 | F | 4 |
| Harry | 60 | M | 3 |
We want to create a vertical summary that shows how many times each gender and rating appears in the table. To do this, we can use the COUNTIF function as follows:
- In cell F1, we enter the formula
=UNIQUE(C:C)to get the unique genders from column C. The formula returns an array of {“F”;“M”}, which spills into the cells F2:F3. - In cell G2, we enter the formula
=COUNTIF(C:C,F2#)to count the occurrences of each gender in column C. The formula uses the spill reference operator (#) to refer to the array returned by the UNIQUE function in F2:F3. The formula returns an array of {4;4}, which spills into the cells G2:G3. - In cell H1, we enter the formula
=UNIQUE(D:D)to get the unique ratings from column D. The formula returns an array of {4;3;5}, which spills into the cells H2:H4. - In cell I2, we enter the formula
=COUNTIF(D:D,H2#)to count the occurrences of each rating in column D. The formula uses the spill reference operator (#) to refer to the array returned by the UNIQUE function in H2:H4. The formula returns an array of {3;3;2}, which spills into the cells I2:I4. - We add labels to the summary table, such as “Gender”, “Count”, “Rating”, and “Count”, and format the table as desired. The final result looks like this:
| Gender | Count | Rating | Count |
|---|---|---|---|
| F | 4 | 4 | 3 |
| M | 4 | 3 | 3 |
| 5 | 2 |
Result
The result of the scenario is that we have created a vertical summary from columns with count using the COUNTIF function. The summary table shows how many times each gender and rating appears in the survey data. This can help us analyze the data and draw some insights. For example, we can see that the average rating is 4, and that there is no difference in the number of responses between genders.
Other approaches
Besides using the COUNTIF function, there are other ways to create a vertical summary from columns with count in Excel, such as:
- Using pivot tables, which are interactive tables that can summarize and analyze data. Pivot tables can automatically group and count data by any column, and can be easily sorted and filtered. However, pivot tables require a separate worksheet or area, and need to be refreshed to show the latest data.
- Using dynamic arrays, which are new features in Excel that allow formulas to return multiple values that spill into adjacent cells. Dynamic arrays can use functions like UNIQUE, SORT, and FILTER to create summary tables that update automatically when data changes. However, dynamic arrays are only available in the latest versions of Excel, and may not be compatible with older versions or other applications.
- Using VBA code, which is a programming language that can automate tasks and manipulate data in Excel. VBA code can loop through the columns and count the unique values, and write the output to a summary table. However, VBA code requires some programming skills, and may not be easy to modify or maintain.
