+ Reply to Thread
Results 1 to 10 of 10

To calculate % based on subtotal

  1. #1
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    175

    To calculate % based on subtotal

    Dear All,

    PFA the file for your ref.

    I need to calculate % in col H.
    Doing it manually is very time consuming since data is huge.

    Please assist.
    Attached Files Attached Files
    Last edited by AliGW; 08-05-2018 at 10:52 AM. Reason: Moved to appropriate forum section.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,151

    Re: To calculate % based on subtotal

    Hi,

    Put this in H9 and pull it down.

    =+G9/SUMIF($D$9:$D$21,D9,$G$9:$G$21)
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    175

    Re: To calculate % based on subtotal

    Marvin,

    Sorry to tell u that formula does not work.
    It does not give the desired result as mentioned in col. H.

    Please assist.

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,419

    Re: To calculate % based on subtotal

    In "H9"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In "H17"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Copy paste according data record.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  5. #5
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    175

    Re: To calculate % based on subtotal

    Sorry brother.
    Currently I have put this formula only.

    In my data, there are approx 25000 rows.
    So this formula of yours would be time consuming and too manual.

    Is there any other formula which can reduce my manual work?

    Please assist ..

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,419

    Re: To calculate % based on subtotal

    Thanks dear, in this case can you share / attach your file with desired required result.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,930

    Re: To calculate % based on subtotal

    Try this. I am not so sure it will perform well over 25,000 rows.

    Please let us know.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Note: I tried without success to get this to work.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It didn't and I am mystified why. According to my online source MATCH in Excel 2003 should accept wildcards. It didn't in your upload.

    Update: This works. I suspect it will perform best of all.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 08-05-2018 at 08:46 PM. Reason: simplified formulas
    Dave

  8. #8
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,419

    Re: To calculate % based on subtotal

    If your data record more than 25000. I suggest you to change little bit data structure.
    Refer attach file.
    In this file i have created summary sheet with unique "Sol ID" & Sol Desc" & sum of total with sumifs formula.
    In Data sheet "F2" formula is :
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copy paste down.

    In this case you avoid manually work & time saving.
    hope this will helpful you & your problem resolved.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    175

    Re: To calculate % based on subtotal

    Thanks a tonne....
    You guys made my days.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,930

    Re: To calculate % based on subtotal

    You are welcome. Thank you for the feedback and marking this Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1