+ Reply to Thread
Results 1 to 7 of 7

Substract values in two columns in filter mode

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Substract values in two columns in filter mode

    Hello everyone
    I have data in range("C3:F14")
    I will filter my range and after filter I need to subtract column E - Column D (for visible cells only)
    For example: I have filtered data and only rows 5 - 9 - 13 are visible
    In Column B I need to substract E9-D5 and the result will be in B5
    And E13-D9 and the result to be in B9
    Hope it is clear
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Substract values in two columns in filter mode

    You will need a helper column for this.

    First make all cells rows visible.

    I am using Column H as helper column.

    In cell H5 enter this formula-
    Please Login or Register  to view this content.
    copy down...

    Now In cell B5-
    Please Login or Register  to view this content.
    copy down...

    Workbook attached for reference.
    Attached Files Attached Files
    Last edited by sourabhg98; 02-22-2016 at 10:26 AM.
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Substract values in two columns in filter mode

    Mr. Sourabh
    Thanks a lot for this great and wonderful solution using formulas ..
    Is it possible to create UDF for this purpose and get rid of any helper columns?
    Best Regards

  4. #4
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Substract values in two columns in filter mode

    Hi,
    Check this UDF-
    Please Login or Register  to view this content.
    Formula in B5-
    Please Login or Register  to view this content.
    copy down...

    See if this helps!
    Check attached for reference.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Substract values in two columns in filter mode

    Great and wonderful udf function
    Thank you very much
    I just need a little fix for the results .. need to get rid of the last result in last visible row ..as done in formula

  6. #6
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Substract values in two columns in filter mode

    Change UDF to-
    Please Login or Register  to view this content.
    and now instead of the old formula use this one in B5-
    Please Login or Register  to view this content.
    Copy down....

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Substract values in two columns in filter mode

    Yes that's perfect now
    You are amazing ..
    Thank you very much for this awesome help
    Best Regards

+ 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 substract two dates columns and count for specific days
    By Zuluwaco in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-26-2015, 10:53 AM
  2. Select which columns to substract/compare
    By dhen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-13-2015, 05:43 PM
  3. [SOLVED] Faster Way to Substract 2 Columns?
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-22-2015, 04:47 PM
  4. Replies: 6
    Last Post: 10-16-2014, 08:42 AM
  5. [SOLVED] How to substract values in one column from another in every possible combination?
    By Alice_1989 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-03-2012, 04:38 PM
  6. [SOLVED] Not showing blank and non blank items in filter mode for values
    By Bhaskar Polisetty in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-20-2006, 09:10 AM
  7. How do I substract numbers in range like this: Substract(a1..a10)
    By Paulo in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 11-17-2005, 12:50 AM

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