Heading: It is the name of the column that you want to sum. Now the generic formula to sum the matching column will be: = SUM( INDIRECT(heading)) It will name all the data columns as their headings. It will open a dialog to create a name from the ranges. Select the table including the headings and press CTRL+SHIFT+F3. In this method, we first need to name the columns as their heading names. Method 3: Sum Whole Column in Table Using Named Range and INDIRECT functionĮverything get's simple if you name your ranges as column headings. Now the formula is = SUMPRODUCT((C3:N7)*)Īnd finally, we get the sum of these values. In the formula, the statement C2:N2=B10 returns an array that contains all FALSE values except one that matches B10. Without further delay let's use the formula. Heading: It is the heading that you want to match. Headers: It is the header range of columns that you want to sum. The syntax of the SUMPRODUCT method to sum matching column is: = SUMPRODUCT((columns)*(headers=heading))Ĭolumns: It is the 2-dimensional range of the columns that you want to sum. Method 1: Sum Whole Column in Table Using SUMPRODUCT function.
If we change the month in B10, the sum should change and returns that month's sum, without changing anything in the formula. Now the task is to get the sum of given month's sales in Cell C10. Here I have a table of sales done by different salesmen in different months. Unlike other articles, let's see the scenario first. If you want to get the sum of a column by just using the column name, you can do this in 3 easy ways in Excel.
0 kommentar(er)
