Create a custom SUM function with VBA

[goncalogera]

Hi there,

I have a table called ("Vendas2020") that has the daily sales and each row has either "C" or "V" if they're either a purchase or a sale (C/V column is the fifth one and is called "Compra / Venda"). I have inserted a function to only calculate the visible values in the table since the destination worksheet (which is a report called "Relatório") has slicers for the products, salesperson and year. The code for the sumfunction is as follows:

Please Login or Register  to view this content.

I'd like to add an if statement (or something like it) that only sums the values if they're purchases ("V") but I can't seem to make it work. It either throws a value error or a spill error. I can't seem to find a thread that has a solution that might apply in this case. I know it's something simple but I'm somewhat new to VBA.
Thanks in advance!

[TMS]

Why are you using a volatile VBA function? You could just use SUBTOTAL. For example,

Please Login or Register  to view this content.

[goncalogera]

I'm trying not to use a SUBTOTAL because the function must be flexible to assume certain filters as fixed and others that vary from column to column. Since the same function will be used in different column, SUBTOTAL can only sum the visible cells and not select them. What I need is to add to my code a line that selects values based on different criteria (for example, only select "V").

[TMS]

Please post a sample workbook. I don't understand why SUBTOTAL won't work for what you are describing.

[achammar]

Try this for your function:

Please Login or Register  to view this content.