We may often need to analyse data on filtered range. There may be requirement to count the cells, or sum the cells or find the maximum value of the cells or so on... In such situation only using the FUNCTIONs simply like SUM, MAX etc.. will not work. However if we use the function with their name like 3(for non blank cells i;e COUNTA) or 9(for summing the filtered range i;e SUM) or others as per actual requirement which are in built and available in excel, with SUBTOTAL function then it works perfectly. For your clear understanding please refer the above table. The table contains of different zone name and respective quantity field. Suppose if we want obtain total quantity of "east" zone then we need to use name of SUM function (which is 9) using the name range or reference with SUBTOTAL. Let filter the zone for "east". The function to be used like =SUBTOTAL(9,B2:B7) and the result will be appear 44. Here using simple SUM will give the result as 88.It is very useful where the filtered data is huge.
0 Comments
|
|