+ Reply to Thread
Results 1 to 3 of 3

How to Calculate Percent Split of Total Value Separated by Two Sets of Criteria

  1. #1
    Forum Contributor garrett.grillo's Avatar
    Join Date
    06-19-2011
    Location
    San Francisco, California
    MS-Off Ver
    Excel 2010
    Posts
    188

    How to Calculate Percent Split of Total Value Separated by Two Sets of Criteria

    Hi Excel Forum Team,

    Please see the excel example attachment/link below for help that I need with a formula that calculates the percentage split in the Service Order Single or Split list (column D) between two sets of criteria, the first being the WBS Element list (column B) and the second being the Service Order Number list (column C). Then based upon the Service Order Contract Value list (column E), the needed formula contained in the Service Order Single or Split list (column D) will automatically show the percent split in relation the Service Order Contract Value list subtotals.

    The defining difference between weather a service order is counted as either a single (100%) service order or if it is a split service order (<100%) is directed by the WBS Element list numbers.

    If in each row the WBS Element list (cells B3:B13) have similar numbers (for example: 00542-100) with either similar or dissimilar numbers in the Service Order Number list (cells C3:C13), then the Service Order Single or Split list formulas (cells D3:D13) will result in 100%. In this case, with similar WBS Element numbers, it really does not matter what’s in the Service Order Number list cells.

    However, If in each row the WBS Element list cells have dissimilar numbers (for example: 00542-200 and 00542-900) with the Service Order Number list cells have similar numbers (for example: B123 and B123), then the Service Order Single or Split list cell formulas will result in fragmenting the percentage (for example: 00542-200 & B123 = 60% and 00542-900 & B123 = 40%) of the total 100%. This percent split is based off of the Service Order Contract Value list (cells E3:E13) where the division of the total service order sum is split accordingly. In this case, the combination of dissimilar WBS Element numbers with similar Service Order Numbers triggers the split to occur.

    Please feel free to modify or add to the original excel example attachment/link in order to help make the requested formula(s) work.

    Thank you for all your help!

    Calculate Percent Split of Total Value with Two Criteria.xlsx

    Garrett

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: How to Calculate Percent Split of Total Value Separated by Two Sets of Criteria

    Try in D3 then drag down:

    Please Login or Register  to view this content.
    Quang PT

  3. #3
    Forum Contributor garrett.grillo's Avatar
    Join Date
    06-19-2011
    Location
    San Francisco, California
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: How to Calculate Percent Split of Total Value Separated by Two Sets of Criteria

    Hi Bobo021999,

    That's exactly the formula I needed and it works perfectly when inserting it into the example model that's attached/linked above!

    Thank you so much for taking the time to in arriving at your awesome solution and being a great help to me.

    Garrett

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sum Total Based On Four Different Sets of Criteria
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 10-02-2014, 10:15 PM
  2. [SOLVED] Calculate total of a column, based on criteria and copy to another sheet
    By luke.guthrie in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 06-01-2012, 05:59 PM
  3. Multiple criteria sum to calculate total amount per annum"
    By chunlee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-27-2010, 08:30 AM
  4. [SOLVED] HOW TO CALCULATE 2/10 OF 1 PERCENT OF A TOTAL NUMBER?
    By AMANDA RILEY in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-26-2006, 08:10 PM
  5. [SOLVED] calculate commission $ based on total sold and commission percent
    By blondeindenver in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-05-2005, 10:05 PM

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