Sumifs()

This formula helps to sumup based on multiple criteria. It is same as sumif but sumif support only one condition whereas sumif support multiple conditions

=SUMIFS(sumrange, criteriarange1, criteria1,criteriarange2, criteria2, criteriarange3, criteria3, ....)

In example in the screenshot above the sumifs is calculating based on two criteria first is invoice number and the second is product type.

Count by color

This addin create a formula called countbycolor that helps to count all the values in same Color you select as criteria.

=Countbycolor(Criteria,Count range)

Criteria- The cell with the Color you want to count.

Count Range= Is the range of the for which you want the total count.

Click here to download addin

Sum by Color

This addin create a formula called sumbycolor that helps to sum up all the values in same Color you select as criteria.

=Sumbycolor(Criteria,Sum range)

Criteria- The cell with the Color you want to sum up.

Sum Range= Is the range of the for which you want the total.

Click here to download addin


To add the addin go to

File-Options-addins-GO(at the bottom)-browse-paste the addin-double click on it-ok.

Hyperion Retrieve by Macro

Declare function essmenuvconnect lib "essexcln.xll" () as long

Declare function essvconnect lib "essexcln.xll" (byval sheetname as variant, byval username as variant, byval password as variant, byval server as variant, byval application as variant, byval database as variant) as long

Sub hype()

' To connect to essbase cube
G= essvconnect(sheetname,username, password,server name, application name, database name)

' To Retrieve data
Selection.application.run macro:="essmenuretrieve"

End sub

The details about server name and other you will get in login pop-up of essbase.

Indirect()

This formula is used to get a cell value for cell name referred in a cell. This formula can be used in conditional formatting and with other formulas.

=INDIRECT(CELL NO, A1 Style/ R1C1)

Cell No= Cell with cell Name.
A1 style= Normal cell referencing
R1C1= One we use in macro. R is row & C is column. Here R then row number , C then Column Number.

Defining variable value by excel formula

Application.Worksheetfunction.formulaname

This will help while defining loops. A variable can be defined for the number of times loop should run. This can be done by defining formula to variable.

Application Screen Updating

This Macro code will make macro run faster by avoiding displaying the background view of running macro. You will not see anything happening in the background while the macro is running.

Sub xyz()

Application.screenupdating =False

Your Codings

Application.screenupdating =True

End sub