+ Reply to Thread
Results 1 to 12 of 12

Subtotal based on conditions met in another column

  1. #1
    Registered User
    Join Date
    06-01-2017
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    9

    Subtotal based on conditions met in another column

    I need help trying to figure out how to make a formula that will create a subtotal (sum) that will only add visible fields when using filter.

    What I need it to do is: Create a subtotal of all values from column C (Span Code) when column B (Span Type) is not "Manual - Climbing", then take all values that ARE "Manual - Climbing", add them together and multiply them by 3, and add them to the first total.

    All while not adding rows that are filtered out by the company column A.

    I've been able to create partial formulas using SUMIFS, but they don't work with filtered data... Is there any way to do this?

    So basically, (sum spantype <> Manual) + (sum spantype = Manual, subtotal *3) in case I wasn't clear. xD
    Attached Files Attached Files
    Last edited by Lunaki; 06-06-2017 at 06:31 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Subtotal based on conditions met in another column

    Where do you want the answer? This will put it in a message box:

    Please Login or Register  to view this content.
    Directions for running the routine(s) just supplied

    If you haven't used macros before you'll need to go to:
    File- options - trust center -trust center settings - macro settings ,
    the second option down (disable all macros with notification)

    Then - Copy the code to the clipboard

    Open your Workbook

    Press ALT + F11 to open the Visual Basic Editor.

    Select "Module" from the Insert menu

    Type "Option Explicit" then paste the code under it

    With the cursor between Sub and End Sub press F5 (F8 to Single Step)

    OR

    Press ALT + Q to close the code window.

    Press ALT + F8 then double click on the macro name
    *Be sure to save the book with the code as Macro-Enabled
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    06-01-2017
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    9

    Re: Subtotal based on conditions met in another column

    I'm sorry but I'm not sure what you're referring to. xD

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Subtotal based on conditions met in another column

    With a pivot table.

    See the attached file.

    Notice => you don't add the expected result in your sheet.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Subtotal based on conditions met in another column

    Hi xD,

    Where do you want the sum to display, how would you like it displayed?

    Or, do you mean the macro and VBA???

  6. #6
    Registered User
    Join Date
    06-01-2017
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    9

    Unhappy Re: Subtotal based on conditions met in another column

    Oh, sorry. What I would like it to display is just the subtotal off to the side somewhere. The problem I was facing was that subtotal and IF statements don't seem to work together.

    The file I've uploaded now has a better explanation of what I'm hoping to accomplish. Sorry about not being super clear with it.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Subtotal based on conditions met in another column

    What is the differance with the question, in which I offered a solution in # 4.

    So please explain what is wrong in my solution.

  8. #8
    Registered User
    Join Date
    06-01-2017
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    9

    Re: Subtotal based on conditions met in another column

    When I used the filter, the total didn't change in the table you created. So for example, when i filtered the company "Kildonan Tree Service" out, the totals remained the same.

    I need the table to give a total based on filtered results, so that it does not include values that are not visible.

    Thank you for your efforts, sorry I didn't respond.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Subtotal based on conditions met in another column

    And did you try the filter in the pivot table?

    You don't show the result manualy,so I can't show it to you.

  10. #10
    Registered User
    Join Date
    06-01-2017
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    9

    Re: Subtotal based on conditions met in another column

    Oh! I've never used pivot tables before. I didn't notice that you could turn on the Company field to see the results from that.

    Yes, that gives me the result I was looking for. Thanks so much!

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Subtotal based on conditions met in another column

    This will put them in the next row under H,I & J:

    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Subtotal based on conditions met in another column

    Glad to help.

    If the question is solved will you mark the question solved.

    It is also appreciated if you click on the star on the left side of the window (add reputation) to thank the forummembers who helped you.

+ 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] How to subtotal an excel column based on specific data of another column ?
    By talib.essami in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-20-2016, 09:35 PM
  2. Replies: 4
    Last Post: 07-07-2015, 12:10 AM
  3. [SOLVED] Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.
    By RobertOHare in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-09-2013, 02:57 PM
  4. subtotal one column based on the range of two other columns
    By jshot99 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-31-2011, 03:55 PM
  5. Replies: 2
    Last Post: 02-14-2011, 03:52 PM
  6. [SOLVED] subtotal based on two conditions
    By ashish128 in forum Excel General
    Replies: 5
    Last Post: 04-28-2006, 12:20 PM
  7. Subtotal database based on column with faked date
    By JonasP in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-05-2006, 11:10 AM

Tags for this Thread

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