• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Subtotals and totals by filter

S

Serene

Member
Dear Guys


I have a table with filters by dept.

Please advise if I can create a function based on the filter that is being created.

For eg, if the filter is dept 1, then the subtotal and total will change automatically.

Likewise for selecting all.


Thanks in advance

Serene
 
@Serene


Hi


you can use the SUBTOTAL FORMULA


=SUBTOTAL(9,b1:b10)


Hope it will give some idea otherwise try to upload a sample workbook


Thanks


SP
 
Thanks for the quick response!

However, the formula is not giving me the correct value. It is actually returning a larger sum when I filter. Is there any settings needed for the filter?


Thanks

Serene
 
@Serene


Hi


actually there is no need to set the settings for the filter


ok, is it possible to upload a sample workbook with the same problem


Thanks


SP
 
Hi Serene,


Try this:


=SUBTOTAL(109,b1:b10)


The formula will find sum of values in visible rows only, neglect the hidden rows.


If you still face any issue, please consider uploading a file here.


http://chandoo.org/forums/topic/posting-a-sample-workbook


Kaushik
 
Hi Serene ,


The SUBTOTAL function has two versions , one of which uses the numbers 1 through 11 , while the other uses the numbers 101 through 111 as the first parameter.


For example , SUBTOTAL(9,....) does a SUM of the range mentioned as the second parameter ; so does SUBTOTAL(109,....) ; the difference between the two is that if some rows within the range to be summed up are hidden , the SUBTOTAL(9,....) version will return the same result , while the SUBTOTAL(109,....) version will exclude the hidden rows.


You can try using SUBTOTAL(109,....) instead of SUBTOTAL(9,....) and see.


Narayan
 
Thanks guys for the advice.

Actually, the table concerned is a summation of all the sheets in the workbook.

I tried the alternative formula but the result is still the same.


I will try to upload the worksheet


Thanks

Serene
 
You may want to look at the AGGREGATE function, with Option 3 or 5


http://office.microsoft.com/en-us/excel-help/aggregate-function-HA010338704.aspx
 
Dear Guys

I was able to get the correct answer with all of the above formulas.

There was a formatting problem. Thanks everyone!


Serene
 
You must log in or register to reply here.
Back
Top