Nov 13 2020 06:55 AM
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:
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!
Nov 13 2020 07:07 AM
1. VBA SUMIF based on Variables
2. WorksheetFunction.SumIf method (Excel)
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.sumif
Hope I was able to help you.
Nikolino
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
Nov 13 2020 09:05 AM
Solution
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.
Nov 13 2020 09:14 AM
@NikolinoDE Thank you very much for your answer but I'm trying to create my own custom SUBTOTAL function
Nov 13 2020 09:17 AM - edited Nov 13 2020 09:18 AM
@JMB17Thank you very much for all your work! I don't know if my line of thought is correct but can the subtotal function perform "multiple ifs"?
Nov 13 2020 09:37 AM
Nov 13 2020 09:42 AM
@JMB17 Got it, I'll try it then! Thanks again for your help!
Nov 13 2020 09:05 AM
Solution
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.