+ Reply to Thread
Results 1 to 17 of 17

VBA Function to sum rows based on condition

  1. #1
    Registered User
    Join Date
    07-22-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    12

    VBA Function to sum rows based on condition

    Hello All,

    I am new to the Forums and I have a question I hope you can help with.

    I am trying to create a custom function that will look up the last row with a certain text and sum the values contained in an adjacent column.

    It's important that the Function be able to take the UniqueId as a paramater.

    I am really stumped on how to approach this. I have attached an Excel file with a working example of what I am trying to achieve.

    Any help would be greatly appreciated.

    Sarah
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    697

    Re: VBA Function to sum rows based on condition

    What is your criteria for selecting the "yellow" colored range?

  3. #3
    Registered User
    Join Date
    07-22-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    12

    Re: VBA Function to sum rows based on condition

    Haluk, thank you very much for the reply!

    The aim is to be able to call FunctionA(UniqueId) and it will sum all values per unique id that are below the text.

    thank you for your help!

    Sarah

  4. #4
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    697

    Re: VBA Function to sum rows based on condition

    You can try this macro if there isn't any criteria;

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-22-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    12

    Re: VBA Function to sum rows based on condition

    Haluk, thanks again for the reply!

    What I need is to find the last "yes" in column D and sum all the values that are "no" per unique Id.

    So in a different sheet, I will be able to call FunctionSomething("AA11") and it will return all the values per criteria above.

    hope this makes sense, really appreciate your time and help!

    Sarah
    Last edited by SarahK2000; 07-22-2020 at 06:05 AM.

  6. #6
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    697

    Re: VBA Function to sum rows based on condition

    Or, you can use a UDF like this:

    Please Login or Register  to view this content.
    Use the function as:

    Please Login or Register  to view this content.
    where B12:C17 is the range you are dealing, and K13 is the ID.

  7. #7
    Registered User
    Join Date
    07-22-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    12

    Re: VBA Function to sum rows based on condition

    Thanks Haluk!

    I just need to be able to find the last "yes" and sum all values that are "no" using the UDF you have created (which is extremely good by the way).

    many thanks for your time!

    Sarah

  8. #8
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    697

    Re: VBA Function to sum rows based on condition

    You're welcome Sarah;

    BTW; the attached file includes a UDF where the last string "Yes" is found within the function and then calculates the sum values.
    Attached Files Attached Files
    Last edited by Haluk; 07-22-2020 at 09:04 AM. Reason: improvement of code...

  9. #9
    Registered User
    Join Date
    07-22-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    12

    Re: VBA Function to sum rows based on condition

    Thanks Haluk, you're the best!

  10. #10
    Registered User
    Join Date
    07-22-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    12

    Re: VBA Function to sum rows based on condition

    Hi Again,

    I keep getting a #value error when updating the spreadsheet, any idea how to get around this?

    Thank you

    Sarah

  11. #11
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    697

    Re: VBA Function to sum rows based on condition

    Remove the below line in the revised workbook @ message#8 and then, try again please.

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    07-22-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    12

    Re: VBA Function to sum rows based on condition

    Thanks Haluk, I'm actually not at my computer any longer.

    I will try your new code tomorrow - really appreciate your help with this!

    The UDF in the test spreadsheet appears different to the one I have - the one I have only has one paramater (UniqueId).

    I also have an option explicit

    Many Thanks

    Chat soon.

    Sarah

  13. #13
    Registered User
    Join Date
    07-22-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    12

    Re: VBA Function to sum rows based on condition

    Good morning!

    I am having difficulty adapting the UDF.

    I would be extremely grateful if you could declare all the variables and put sheet names and some comments in the example that you kindly provided.

    Once again, thanks for your assistance.

    Sarah

  14. #14
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    697

    Re: VBA Function to sum rows based on condition

    Good morning Sarah;

    Add the following declarations for variables:

    Please Login or Register  to view this content.
    The usage is:

    Assuming that your data table is in the range B2:D17, enter the IDs manually in cells K13:K15 or wherever you want. Then, next to these cells enter the UDF as:

    Please Login or Register  to view this content.
    The first parameter of the UDF is the range housing Yes/No values which is D$3:D$17. The second parameter is the ID which you want the get the sum values.

  15. #15
    Registered User
    Join Date
    07-22-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    12

    Re: VBA Function to sum rows based on condition

    Good morning Haluk,

    you're up early!

    Apologies for wasting your time, as I'm sure you can tell I'm very new to VBA.

    I have attached my test version, I keep getting the #value...

    As I keep saying, appreciate your help and time.

    Sarah
    Attached Files Attached Files

  16. #16
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    697

    Re: VBA Function to sum rows based on condition

    Hi Sarah;

    UDF is revised as below:

    Please Login or Register  to view this content.
    You can try the attached workbook...
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    07-22-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    12

    Re: VBA Function to sum rows based on condition

    Thanks Haluk!

    Works as intended, thanks for your help...and patience.

    Sarah

+ 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] If Function based on Certain Condition
    By macro12 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-27-2015, 04:32 AM
  2. Macro copies rows based on condition, BUT rows copy in Wrong order. PLEASE HELP!
    By JAllard1990 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-16-2014, 04:33 PM
  3. How to delete rows based on a met condition between two rows
    By dijimbob in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-23-2014, 05:11 PM
  4. Duplicate rows based on condition
    By booo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-08-2010, 06:34 AM
  5. Copy Rows based on a Condition
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2010, 08:58 AM
  6. Deleting rows based on condition
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-10-2008, 04:27 AM
  7. Hide rows based on a condition
    By KimberlyH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2006, 04:10 PM

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