Some of Advanced Formulas & Tips

Excel is the very useful and important resource so i thought let I may share with you some for the useful advance excel tips and formulas with u


• Multi-Conditional Count/Sum:
(Count)
Synt. =SUMPRODUCT(--(Rng_1=Val1), --(Rng_2=Val2))

ex. =SUMPRODUCT(--(A2:A100 = “abcd”), --(B2:B100 = 1234))

(Sum)
Synt. =SUMPRODUCT(--(Rng_1=Val1), --(Rng_2=Val2), Rng_to_Sum)

ex. =SUMPRODUCT(--(A2:A100 = “abcd”), --(B2:B100 = 1234), H2:H100)

Important: All ranges must be of equal size. Also, cannot use whole-column ranges such as A:A.

Note: Multi-Conditional Count and Sum can be accomplished with a Pivot Table but for dynamic spreadsheet design these formulas are excellent (but memory hogs.) Also, up to 30 conditions when counting, 29 conditions when summing (plus one range to sum.)

• INDEX/MATCH Combination (HLOOKUP/VLOOKUP alternative):
Synt. =INDEX(Rng, MATCH(Val_to_Look_up, Lookup_Rng, [0 or 1]), [Col_Number])

ex. =INDEX(A:C,MATCH(“abcd”,A:A, 0), MATCH(123,1:1, 0))

Note: The “[ ]” in Excel means that this argument is optional.

Explantion: This is more versatile than HLOOKUP/VLOOKUP. With the INDEX/MATCH combination you can use it as a substitute for HLOOKUP or VLOOKUP, -or-, it can be used to look across columns and down rows for a value, as the above example illustrates. So, the above formula will look down column A until it finds “abcd” and then it will look across columns in row 1 until it finds the value 123. It will then return the value at the “abcd”/123 intersection.

• Determine Last Used Row:
(Numeric)
ex. =INDEX(A:A, MATCH(9.99999999999999E+307,B:B))

(Alpha)
ex. =INDEX(A:A,MATCH(REPT(“z”,255),B:B))

• Using Last Used Row in a Formula:
ex. =SUM(A2:INDEX(A:A,MATCH(9.99999999999999E+307,B:B)))
or
ex. =SUM(A2: INDEX(A:A,MATCH(REPT(“z”,255),B:B)))

Note: This technique is important for faster formula calculation, as in when performing multi-conditional Counts and Sums because you want to use the smallest range possible. Also, aids in dynamic spreadsheet design because the ranges don’t need to be manually altered.

• Unique Value Counting
Synt. =SUMPRODUCT((Rng_1<>"")/COUNTIF(Rng_1, Rng_1&""))

ex. =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

Note: Use the smallest range possible due to slow calculation times. See Using Last Used Row in a Formula above.

• Conditional Count/Sum Across Multiple Sheets
1. On the Worksheet Menu Bar go to Insert/ Name/ Define
2. In the “Names in workbook:” inputbox, type: Shts.
3. In the “Refers to:” inputbox, type: ={“Sheet1”, “Sheet2”, “Sheet3”} (use the actual names of the worksheets.)
4. Now you can use the following formulas to conditionally count or sum across worksheets:
ex. =SUMPRODUCT(COUNTIF(INDIRECT(Shts&"!A:A"),">0"))
or
ex. =SUMPRODUCT(SUMIF(INDIRECT(Shts&"!A:A"),">100",INDIRECT(Shts&"!A:A")))