Filtering Data & Calculating AutoSum
Filtering Data & Calculating AutoSum is a very helpful feature. When you use AutoSum function it adds up both the hidden and the seen cells. So to avoid totaling errors we must first filter the data and then use Sum to total the column. We can use COUNT and SUM function to count data in Excel but these function will not work correctly work when u use filtered data. Filtering Data & Calculating AutoSum
Ways to filter Data and to use the Sum function | Data filter करने और Sum फ़ंक्शन का उपयोग करने के तरीके
1st Method: The first method is to temporarily view the subtotal for selected cells in the status bar at the bottom of the Excel. But before viewing the subtotal we need to filter the data. We can filter the data by selecting the entire table.
Go to the Data tab, click on filter.
Filter box will display and it will ask you to select the column that you want to filter.
Apply filter and all the data will be filtered and will be shown in the spreadsheet.
Now select the column you want to total and temporary total will appear at the bottom right in the status bar of Excel.
2nd Method: The 2nd method is to use the SUBTOTAL function. This function is used only for filtered and visible cells.
So first we need to filter the data by following the same steps as explained above in method 1.
After you have filtered the data, select the cell at the bottom of the table. Type the SUBTOTAL function, indicate 9 for the function_num, and select the range of cells in the table. It will display the total correctly.
3rd method: The 3rd method is to filter the data and then go to the AutoSum drop down menu and select sum in it. The sum formula will be entered and the n select the cells. You will be able to see the total correctly.
If you would have used AutoSum the total would not be correct as it would have totaled the hidden cells as well.
If you have a complaint about Filter Data & Calculating AutoSum, you can post in the comment box below. If you like Filter Data & Calculating AutoSum, then like it.
Conclusion:
This is a must trick to be learnt in Excel as it is of great help and very useful. It is of utmost importance as well as it helps you to know the flaws if you do not take care while calculating the sum when you filter the data