Excel is a powerful tool for working with data, but sometimes you may need to perform calculations that involve looking up values in a table and summing them based on certain criteria. For example, you may have a table of sales data for different products and months, and you want to find the total sales for a specific product or a specific month. How can you do that in Excel?
One way to do this is to use a combination of the SUM, INDEX, and MATCH functions. These functions can help you look up values in a table by using the name of the respective column, and then sum the values in a certain range. In this article, we will explain how these functions work and how to use them in a formula. We will also show you a practical example with real numbers and an Excel table. Finally, we will discuss some other approaches that you can use to achieve the same result.
Before we dive into the formula, let’s review the basic theory behind the functions that we will use.
SUM Function
The SUM function is one of the most basic and common functions in Excel. It simply returns the sum of a group of numbers. You can use it to add numbers, cell references, ranges, arrays, and constants, in any combination. For example, the formula =SUM(1,2,3) returns 6, and the formula =SUM(A1:A10) returns the sum of the values in the range A1:A10.
The syntax of the SUM function is:
=SUM(number1, [number2], ...)
number1– The first number or range to add. This argument is required.number2, … – Additional numbers or ranges to add. These arguments are optional. You can enter up to 255 arguments.
INDEX Function
The INDEX function is a lookup function that returns the value at a given position in a range or array. You can use it to retrieve individual values, or entire rows and columns. The INDEX function is often used together with the MATCH function to provide row and column numbers.
The syntax of the INDEX function is:
=INDEX(array, row_num, [column_num])
array– The range of cells or array constant to look up values from. This argument is required.row_num– The row position in the array to return. This argument is required. If you set it to zero or omit it, the INDEX function returns an array of values for the entire column.column_num– The column position in the array to return. This argument is optional. If you set it to zero or omit it, the INDEX function returns an array of values for the entire row.
MATCH Function
The MATCH function is a lookup function that returns the position of a lookup value in a row, column, or table. The MATCH function supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the MATCH function is combined with the INDEX function to perform more complex lookups.
The syntax of the MATCH function is:
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value– The value to look for in the lookup array. This argument is required.lookup_array– The range of cells or array constant to search for the lookup value. This argument is required.match_type– The type of match to perform. This argument is optional. You can enter 1, 0, or -1. If you omit it, the default value is 1.- 1 means to find the largest value that is less than or equal to the lookup value. The lookup array must be sorted in ascending order.
- 0 means to find the exact match of the lookup value. The lookup array can be in any order.
- -1 means to find the smallest value that is greater than or equal to the lookup value. The lookup array must be sorted in descending order.
Formula Explanation
Now that we have reviewed the basic theory, let’s see how we can use these functions to search and sum a certain range by name of the respective column in Excel formula.
The generic formula is:
=SUM(INDEX(data,0,MATCH(val,header,0)))
Where:
data– The range of cells that contains the data to sum.val– The name of the column to look up and sum.header– The range of cells that contains the column names.
Let’s break down the formula and see how it works.
First, we use the MATCH function to find the column number for the name of the column that we want to sum. For example, if we want to sum the sales for January, we use the formula =MATCH("January",C4:F4,0), where C4:F4 is the range that contains the column names. This formula returns 1, which is the position of January in the range C4:F4.
Next, we use the INDEX function to return the entire column of values for the column number that we found with the MATCH function. For example, if we want to return the values for January, we use the formula =INDEX(C5:F11,0,1), where C5:F11 is the range that contains the data to sum, and 1 is the column number that we got from the MATCH function. This formula returns an array of values for the entire column of January, like this: {6;12;4;10;0;9;6}.
Finally, we use the SUM function to sum the array of values that we got from the INDEX function. For example, if we want to sum the values for January, we use the formula =SUM({6;12;4;10;0;9;6}), which returns 47.
Putting it all together, we get the formula =SUM(INDEX(C5:F11,0,MATCH("January",C4:F4,0))), which sums the values in the column named January in the range C5:F11.
Example
To illustrate the formula, let’s use a practical example with real numbers and an Excel table.
Suppose we have a table of sales data for different products and months, as shown below:
| Product | January | February | March | April |
|---|---|---|---|---|
| Apple | 6 | 8 | 7 | 5 |
| Banana | 12 | 10 | 9 | 11 |
| Cherry | 4 | 6 | 5 | 4 |
| Durian | 10 | 9 | 8 | 7 |
| Eggplant | 0 | 2 | 3 | 4 |
| Fig | 9 | 7 | 6 | 8 |
| Grape | 6 | 5 | 4 | 6 |
The table is located in the range C4:F11, and the column names are in the range C4:F4.
Now, we want to find the total sales for each product and each month. We can use the formula that we explained above to do that.
To find the total sales for Apple, we enter the formula =SUM(INDEX(C5:F11,0,MATCH("Apple",C5:C11,0))) in cell H5. This formula returns 26, which is the sum of the values in the row named Apple in the range C5:F11.
To find the total sales for January, we enter the formula =SUM(INDEX(C5:F11,0,MATCH("January",C4:F4,0))) in cell I6. This formula returns 47, which is the sum of the values in the column named January in the range C5:F11.
We can copy and paste the formulas to the other cells to get the total sales for the other products and months, as shown below:
| Product | Total Sales | January | February | March | April |
|---|---|---|---|---|---|
| Apple | 26 | 6 | 8 | 7 | 5 |
| Banana | 42 | 12 | 10 | 9 | 11 |
| Cherry | 19 | 4 | 6 | 5 | 4 |
| Durian | 34 | 10 | 9 | 8 | 7 |
| Eggplant | 9 | 0 | 2 | 3 | 4 |
| Fig | 30 | 9 | 7 | 6 | 8 |
| Grape | 21 | 6 | 5 | 4 | 6 |
| Total Sales | 181 | 47 | 47 | 42 | 45 |
Other Approaches
The formula that we discussed above is not the only way to search and sum a certain range by name of the respective column in Excel formula. There are some other approaches that you can use to achieve the same result. Here are some of them:
SUMIF Function
The SUMIF function is a conditional sum function that returns the sum of a range of cells that meet a single criterion. You can use it to sum values based on a column name by using a wildcard character (*) to match any text before or after the column name.
The syntax of the SUMIF function is:
=SUMIF(range, criteria, [sum_range])
range– The range of cells to evaluate the criterion. This argument is required.criteria– The condition to test the cells in the range. This argument is required. You can use logical operators, numbers, text, cell references, and wildcards (* ?) to define the criteria.sum_range– The range of cells to sum if the criterion is met. This argument is optional. If you omit it, the SUMIF function sums the cells in the range.
For example, to sum the values in the column named January in the range C5:F11, you can use the formula =SUMIF(C4:F4,"*January*",C5:F11). This formula returns 47, which is the same result as the previous formula.
SUMPRODUCT Function
The SUMPRODUCT function is a versatile function that returns the sum of the products of corresponding ranges or arrays. You can use it to perform array calculations without using an array formula. You can also use it to sum values based on a column name by using a logical expression to compare the column name with the header range.
The syntax of the SUMPRODUCT function is:
=SUMPRODUCT(array1, [array2], ...)
array1– The first array or range to multiply and then add. This argument is required.array2, … – Additional arrays or ranges to multiply and then add. These arguments are optional. You can enter up to 255 arguments.
For example, to sum the values in the column named January in the range C5:F11, you can use the formula =SUMPRODUCT((C4:F4="January")*C5:F11). This formula returns 47, which is the same result as the previous formula.
