To apply a Single Accounting Underline, open the Format Cells dialog and go to the Font tab.
![how to sum a column in excel based on another column how to sum a column in excel based on another column](https://contexturesblog.com/wp-content/uploads/2012/06/sumhours.png)
The other cells in the selection are gray, but thin white lines surround each cell (or group of cells, in the case of the last subtotal and the grand total).Īpply bold formatting to the subtotals using Ctrl+B. The active cell is white, and the remaining cells in the selection are a single block of gray fill.Īfter Visible Cells Only is selected, the active cell has a thin border. Before, there was a single border around the selection. Look for a change in the selection rectangle after you’ve applied Visible Cells Only (or pressed the shortcut). If you continue the next steps without selecting the visible cells, all of the detailed rows will be formatted along with the subtotal rows. It’s important to select Visible Cells Only correctly. Alternately, simply press Alt+ as the shortcut.
![how to sum a column in excel based on another column how to sum a column in excel based on another column](https://i.ytimg.com/vi/RrjuxDe31wo/maxresdefault.jpg)
The long way to find this command is Home, Find & Select, Go To Special, Visible Cells Only, OK. Since your goal is to format only the subtotal rows, you need to use the Visible Cells Only command. Select the numbers in column C.Īt this point, any formatting you add will be applied to both the numbers that you can see and the numbers in the hidden rows. The Subtotal command will bold the words “A1001 Total” in column A, but no special formatting gets applied to the numbers in column C.Ĭlick the #2 Group and Outline button to see only the subtotal and grand total rows. The #2 button will collapse the data and show only the subtotals and grand total. New Group and Outline buttons also will appear to the left of column A. But if you have extra columns in the data, you might need to adjust the settings to choose Sum and the correct columns.Ĭlick OK in the Subtotal dialog, and Excel will insert new subtotal rows in the data. In the Subtotal dialog box, the default settings assume you want to use the Sum function to add a subtotal to the right-most column (in this case, column C) at each change in the first column. Make sure to sort the data by Account Number so that all of the rows for each account appear in adjacent rows.įrom the Data tab, select Subtotals. Now with the help of this function, we want to know the number of sales of HP notebooks in January.Say that your data has Account Number (such as A1001, A1002, etc.) in column A, Description in column B, and Amount in column C. =SUMIFS(sum_range, criteria_range1, criteria1. We don’t need to type two different functions to sum in the function bar. SUMIFS is the sub-category of SUMIF function which adds the cells specified by a given set of conditions or criteria & we can use this function to add multiple criteria in a single function. Using SUMIFS to Sum Based on Column and Row Criteria i) Using SUMIFS to SUM under Multiple AND Criteria Based on Column and Row ➤ SUMIF function finally sums up the sales of HP devices only.ĥ.
![how to sum a column in excel based on another column how to sum a column in excel based on another column](https://media.geeksforgeeks.org/wp-content/uploads/20211029072955/sumifapproach1005.png)
➤ INDEX function then stores all the sales in the column of ‘ Jan’ as reference. ➤ MATCH function looks for the position of the month ‘ Jan’ in the range of cells D4:I4. ➤ Now press Enter & you’ll get the resultant value as 23.