SOLVED

Create a custom SUM(IF) function with VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-1884702%22%20slang%3D%22en-US%22%3ECreate%20a%20custom%20SUM(IF)%20function%20with%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1884702%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20there%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20a%20table%20called%20(%22Vendas2020%22)%20that%20has%20the%20daily%20sales%20and%20each%20row%20has%20either%20%22C%22%20or%20%22V%22%20if%20they're%20either%20a%20purchase%20or%20a%20sale%20(C%2FV%20column%20is%20the%20fifth%20one%20and%20is%20called%20%22Compra%20%2F%20Venda%22).%20I%20have%20inserted%20a%20function%20to%20only%20calculate%20the%20visible%20values%20in%20the%20table%20since%20the%20destination%20worksheet%20(which%20is%20a%20report%20called%20%22Relat%C3%B3rio%22)%20has%20slicers%20for%20the%20products%2C%20salesperson%20and%20year.%20The%20code%20for%20the%20sumfunction%20is%20as%20follows%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFunction%20SUMVisible(Rg%20As%20Range)%0ADim%20xCell%20As%20Range%0ADim%20xCount%20As%20Integer%0ADim%20xTtl%20As%20Double%0A%0AApplication.Volatile%0ASet%20Rg%20%3D%20Intersect(Rg.Parent.UsedRange%2C%20Rg)%0A%0AFor%20Each%20xCell%20In%20Rg%0AIf%20xCell.ColumnWidth%20%26gt%3B%200%20_%0AAnd%20xCell.RowHeight%20%26gt%3B%200%20_%0AAnd%20Not%20IsEmpty(xCell)%20_%0AAnd%20IsNumeric(xCell.Value)%20Then%0AxTtl%20%3D%20xTtl%20%2B%20xCell.Value%0AxCount%20%3D%20xCount%20%2B%201%0AEnd%20If%0ANext%0AIf%20xCount%20%26gt%3B%200%20Then%0ASUMVisible%20%3D%20xTtl%0AElse%0ASUMVisible%20%3D%200%0AEnd%20If%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI'd%20like%20to%20add%20an%20if%20statement%20(or%20something%20like%20it)%20that%20only%20sums%20the%20values%20if%20they're%20purchases%20(%22V%22)%20but%20I%20can't%20seem%20to%20make%20it%20work.%20It%20either%20throws%20a%20value%20error%20or%20a%20spill%20error.%20I%20can't%20seem%20to%20find%20a%20thread%20that%20has%20a%20solution%20that%20might%20apply%20in%20this%20case.%20I%20know%20it's%20something%20simple%20but%20I'm%20somewhat%20new%20to%20VBA.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThanks%20in%20advance!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1884702%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1884780%22%20slang%3D%22de-DE%22%3ESubject%3A%20Create%20a%20custom%20SUM(IF)%20function%20with%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1884780%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F868159%22%20target%3D%22_blank%22%3E%40goncalogera%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F59905397%2Fvba-sumif-based-on-variables%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3E1.%20VBA%20SUMIF%20based%20on%20Variables%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20WorksheetFunction.SumIf%20method%20(Excel)%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Fexcel.worksheetfunction.sumif%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Fexcel.worksheetfunction.sumif%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20was%20able%20to%20help%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1885327%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20custom%20SUM(IF)%20function%20with%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1885327%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F868159%22%20target%3D%22_blank%22%3E%40goncalogera%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20understand%20what%20you're%20trying%20to%20do%20(conditional%20subtotal)%2C%20then%20I%20don't%20think%20you%20don't%20need%20vba.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20an%20example%20file%20-%20if%20you%20filter%20column%201%20for%20either%20%22A%22%20or%20%22B%22%2C%20then%20the%20formula%20below%20the%20table%20will%20subtotal%20the%20visible%20cells%20where%20Compra%2FVenda%3DC.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1885382%22%20slang%3D%22en-US%22%3EBetreff%3A%20Create%20a%20custom%20SUM(IF)%20function%20with%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1885382%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3BThank%20you%20very%20much%20for%20your%20answer%20but%20I'm%20trying%20to%20create%20my%20own%20custom%20SUBTOTAL%20function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1885387%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20custom%20SUM(IF)%20function%20with%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1885387%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3EThank%20you%20very%20much%20for%20all%20your%20work!%20I%20don't%20know%20if%20my%20line%20of%20thought%20is%20correct%20but%20can%20the%20subtotal%20function%20perform%20%22multiple%20ifs%22%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

6 Replies

@goncalogera 

 

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

best response confirmed by goncalogera (New Contributor)
Solution

@goncalogera 

 

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.

 

@NikolinoDE Thank you very much for your answer but I'm trying to create my own custom SUBTOTAL function

@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"?

The subtotal function isn't testing any conditions, it's just one of two arguments for the sumproduct function, which can perform multiple "ifs."

But, the exact syntax will depend on whether you're talking about testing multiple columns for a single value, or testing a single column for multiple values.

@JMB17 Got it, I'll try it then! Thanks again for your help!