SUMIF text comparisons are case insensitive so this cannot be achieved per your requirements. You either need to use a helper column or an array/SUMPRODUCT formula.
Hi,
Firstly, the second line in your code has a typo: it's really important to use Option Explicit to pick mistakes like that up.
Secondly, your code is missing 2 functions: IsEven() and IsOdd(). The VBA object model does not have IsEven() and IsOdd() functions so you have to either implement...
My guess from the OP response is that the requirement is to count the number of colons which occur in a range.
@DashboardNovice
COUNTIF, such as the formula suggested by Faseeh, will count the number of cells which contain a ":". If you want to count the actual number of colons then you can...
So I think the generalised version of my formula would be:
=IFERROR(REPLACE( [STRING] ,1,SEARCH("||",SUBSTITUTE( [STRING] , [CHAR] ,"||", [N] )),""), [STRING] )
Where
[STRING] is the whole string being evaluated
[CHAR] is the character you want to find
[N] is the nth occurrence you of the...
If your SumIntervalCols() function is being used in formula(s) and th(os)e formula(s) it is used in has a dependency on the range changed by your code snippet (somewhere around Summary!A9) then it will cause th(os)e formula(s) to recalculate and your function to be called.
It should be sum([Amount]) rather than sum[Amount]. Additionally, when you use an aggregator function such as SUM in SQL you need to provide a GROUP BY clause. You also need to drop the DISTINCT keyword.
As I mentioned on your other thread (which seems to be very similar), you shouldn't use ADO...
What is the actual error message?
By the way, you shouldn't use ADO to query the workbook the code is in: querying open workbooks with ADO causes a memory leak.
AGGREGATE() is super if you have XL 2010 or later.
These will also work in any version and don't need CSE:
=SUM(SUMIF(E13:G13,{"<0",">0"}))
=SUMIF(E13:G13,"<="&9.99E307)
As Narayan indicated with the Excel Guru link, ideally you would want to save the add-in onto a network share and let the users use it from there. When they install the add-in make sure that they do not choose to copy the add-in locally. If you wanted to you could give them a little VBA code to...