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

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

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

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

Please Login or Register  to view this content.

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.

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

Thanks Haluk, you're the best!

Hi Again,

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

Thank you

Sarah

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

Please Login or Register  to view this content.

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

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

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.

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

Hi Sarah;

UDF is revised as below:

Please Login or Register  to view this content.

You can try the attached workbook...

Thanks Haluk!

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

Sarah