11-13-2020 06:55 AM
11-13-2020 06:55 AM
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:
Function SUMVisible(Rg As Range) Dim xCell As Range Dim xCount As Integer Dim xTtl As Double Application.Volatile Set Rg = Intersect(Rg.Parent.UsedRange, Rg) For Each xCell In Rg If xCell.ColumnWidth > 0 _ And xCell.RowHeight > 0 _ And Not IsEmpty(xCell) _ And IsNumeric(xCell.Value) Then xTtl = xTtl + xCell.Value xCount = xCount + 1 End If Next If xCount > 0 Then SUMVisible = xTtl Else SUMVisible = 0 End If End Function
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!
11-13-2020 07:07 AM
2. WorksheetFunction.SumIf method (Excel)
Hope I was able to help you.
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here
11-13-2020 09:05 AMSolution
If I understand what you're trying to do (conditional subtotal), then I don't think you don't need vba.
I attached an example file - if you filter column 1 for either "A" or "B", then the formula below the table will subtotal the visible cells where Compra/Venda=C.
11-13-2020 09:14 AM
@Nikolino Thank you very much for your answer but I'm trying to create my own custom SUBTOTAL function
11-13-2020 09:37 AM