Excel:SUBTOTAL With SUMPRODUCT and Filter

Everyone knows what is the use of the SUBTOTAL in excel.SUBTOTAL execute a function by ignoring rows by filtering.

But there you have limitations ,SUBTOTAL execute functions those listed only like 1-Average; 2-Count; 3-CountA; 4-Max; 5-Min,.etc as you seeing in the below picture.

If I want to use the SUMPRODUCT function with Subtotal , it is not in the list then how it is possible.

 We have a data sheet as shown in the below picture.

We want to know what is the total sales value of the each store while filtering it.
that is if I am filtering the City main Store then I want a Cell that shows the total sales value of City Main sore.If I Filter Sub store Delhi then the same cell should show total sales value of Sub store Delhi

We have two methods to solve this situation.

 Method-1:Use PRODUCT and SUBTOTAL Separately

1.Insert a Column near to Rate Column ,Named Value
2.Find Value of each Item as shown in Picture:

3.Use SUBTOTAL function in "H3" Cell.   =SUBTOTAL(9,H5:H33)

4.Now Apply Filter and Watch the Change on the Total Sales.

 Showing total value of Sub Store_Delhi Only

  Showing total value of Sub Store_Delhi Only & Sub Store_Mumbai Only

Showing total value of City Main Store Only

If you remove filter you can get Total sales value of all store
you can use filter in Item also,that is you can find total sales of Pen,Bag etc separately
not only that you can use multiple filter also, that is you can easily find the total Sales of Pen in City Main Store like that.

 The above mentioned method is enough to fulfill our requirement,But if we don't like to insert Value Column in our Data Sheet or in other words,we dont have permission to insert a column in our data base then what will be the solution.Don't worry ,go through method -2

 Method-2:Use SUMPRODUCT and SUBTOTAL

 1.Just Place the formula in Cell "G3"  =SUMPRODUCT(F5:F33,SUBTOTAL(9,OFFSET(G5:G33,ROW(G5:G33)-MIN(ROW(G5:G33)),0,1)))

 Copy and paste the above formula in the cell and just edit the range as you wish.

 

 

 2.Now Apply the filter

 Showing total value of City Main Store Only

 Showing total value of Sub Store_Delhi Only

   Showing total value of Sub Store_Delhi Only & Sub Store_Mumbai Only


 

 Showing total value of Bag Item Only

If you remove filter you can get Total sales value of all store
you can use filter in Item also,that is you can find total sales of Pen,Bag etc separately
not only that you can use multiple filter also, that is you can easily find the total Sales of Pen in City Main Store like that.

 The above mentioned method is enough to fulfill our requirement,But if we don't like to insert Value Column in our Data Sheet or in other words,we dont have permission to insert a column in our data base then what will be the solution.Don't worry ,go through method -2