• 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.

Need Grand Total Formula

Dear Friends,

Can you please help me out how can we sum up all the subtotals (Bold Numbers) in attached file by using a formula. I need result as Grand Total in Row 159.

You Kind support in this regard would be highly appreciated.

Thanks in advance,
Kumar
 

Attachments

  • Book1 for Query.xlsx
    15.2 KB · Views: 6
In a simple/lazy approach...

B159=SUMPRODUCT(($C$3:$C$158<>"")*B3:B158)

D159=SUMPRODUCT(($C$3:$C$158<>"")*D3:D158)
 
Hi ,

This is a classic case where you should have used the SUBTOTAL function instead of the SUM function ; the SUBTOTAL function has a property that every SUBTOTAL function will ignore other embedded SUBTOTAL function results.

See the attached file.

Narayan
 

Attachments

  • Book1 for Query.xlsx
    15.7 KB · Views: 6
Dear Mr. Narayan,

Thank you for your reply and answer. As I was pre-occupied with my work schedule I could convey thanks instantly.

I understand that whenever we come across these kind of contexts we need to use subtotal function instead of Sum function for getting result as Grand Total which is sum of all sub totals.

Thank you very much.

Further, my thanks Mr. Deepak too for his quick response.

Thank you all.

Regards,
Kumar
 
Back
Top