Search results

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.

Welcome back @SirJB7! It's good to see you on here again.

Congratulations to all of you!

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...

Microsoft security update MS14-082 is known to cause the error message you describe. See more info here.

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...

Try this formula: =IFERROR(REPLACE(B4,1,SEARCH("||",SUBSTITUTE(B4,":","||",LEN(B4)-LEN(SUBSTITUTE(B4,":","")))),""),B4)

There are lots of ways to do this but, by the sounds of it, the answer you're probably looking for is to use ADO to run queries against the database

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.

Congratulations!

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...

@Berty Sharing the folder and sorting out the permissions is all done manually. Then the Shell command would be like: Shell "\\YourServer\SharedFolder\Script.bat"

If you want to run the bat file on the server from your local machine then you'll need to share the folder it is in and then give yourself permissions to that share. Then in the Vba code you use the server name or DNS alias to qualify the full path to the file. If this is a task which can simply...

It won't make any difference to performance.

Sure, no problem. You are right to be generally sceptical about the efficiency of SUMPRODUCT() formulas. If you're interested in some benchmarking in different sumproduct variations on this style of conditional sum, have a look here.

Why without SUMPRODUCT? If you are worried about slow calculations, the formula I gave you is almost exactly as efficient as your original formula but written more concisely. If your original formula is fine in terms of calculation speed then this one will be fine too. This is because the...

I would go one step further and suggest that one should explicitly define the constant's data type. Constants' values cannot be changed at runtime. Variables' values can be changed at runtime. Static variables are procedure level variables which retain their values between procedure calls.

Like this: =SUMPRODUCT(SUMIFS(APR14.DATA!$O:$O,APR14.DATA!$Z:$Z,$C11,APR14.DATA!$X:$X,'P&L CATS'!$A$1:$A$4))

Many congratulations, Shrivallabha. :)

I mean the colour/shading Excel gives to indicate which cells are selected, not the cell fill colour. I blogged about it here. Prior to XL 2013 it was controlled from the Windows appearance settings.

Hi, No, you cannot customise it with your own colour scheme. In Excel 2013 you cannot even customise the colour of selected cells.