5.1 Validate list of values for uniqueness (Check duplicates)

Assuming, your list is in A1 to A1000. Use following formula to know if list has unique values.

=MAX(FREQUENCY(A1:A1000,A1:A1000))
=MAX(INDEX(COUNTIF(A1:A1000,A1:A1000),,))

If the result is 1, then the values are Unique. If answer is greater than 1, the list has duplicate values.

5.2 COUNTIF on filtered list

You can use SUBTOTAL to perform COUNT on a filtered list but COUNTIF can not be done on a filtered list. Below formula can be used to perform COUNTIF on a filtered list

  =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B20)-ROW(B2),))*(B2:B20>14))

Here B2:B20>14 is like a criterion in COUNTIF (=COUNTIF(B2:B20,">14"))

5.3 SUMIF on filtered list

You can use SUBTOTAL to perform SUM on a filtered list but SUMIF can not be done on a filtered list. Below formula can be used to perform SUMIF on a filtered list

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B20)-ROW(B2),))*(B2:B20>14))

Here B2:B20>14 is like a criterion in SUMIF.

5.4 Calculate MAX count of consecutive entries of a value

Suppose, we want to count maximum times “XYZ” appears consecutively, you may use following Array formula

=MAX(FREQUENCY(IF(A2:A20="XYZ",ROW(A2:A20)),IF(A2:A20<>"XYZ",ROW(A2:A20))))

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER . If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER . This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

5.5 Get File Name using formula

Before getting this, make sure that you file has been saved at least once as this formula is dependent upon the file path name which can be pulled out by CELL function only if file has been saved at least once.

=CELL("filename",$A$1)

5.6 Get Workbook Name using formula

Before getting this, make sure that you file has been saved at least once as this formula is dependent upon the file path name which can be pulled out by CELL function only if file has been saved at least once.

=REPLACE(LEFT(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))-
1),1,FIND("[",CELL("filename",$A$1)),"")

5.7 Get Sheet Name using formula

Before getting this, make sure that you file has been saved at least once as this formula is dependent upon the file path name which can be pulled out by CELL function only if file has been saved at least once. Use following formula

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

Make sure that A1 is used in the formula. If it is not used, it will extract sheet name for the last active sheet which may not be one which we want.
If you want the sheet name for last active sheet only, then formula would become

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

5.8 Get Workbook's Directory using formula

Before getting this, make sure that you file has been saved at least once as this formula is dependent upon the file path name which can be pulled out by CELL function only if file has been saved at least once.
If your workbook is located in say C:\Excel\MyDocs, the formula to retrieve the directory for this would be

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)

5.9 Rank within the Groups

Suppose your have data like below table and you want to know rank of students. You will simple put following formula in D2

=RANK(C2,C2:C100)

But what if you are asked to produce rank of students within each school. Hence, every school's rank will start with 1..
Put following formula in D2 for that case for Descending order ranking. (For ascending order, replace ">" with "<" without quote marks)

=SUMPRODUCT((B$2:B$100=B2)*(C$2:C$100>C2))+1
OR
=COUNTIFS(B$2:B$100,B2,C$2:C$100,">"&C2)+1

5.10 Get column name for a column number

Let's suppose, you have a number in A1 and you want to get the column Name for that.
Hence, if A1=1, you want "A"
Hence, if A1 =26, you want "Z"
Hence, if A1=27, you want "AA" and so on
The formula to derive the column name would be

=SUBSTITUTE(ADDRESS(1,A1,4),1,"")

5.11 Get column range for a column number

Let's suppose, you have a number in A1 and you want to get the column range for that.
Hence, if A1=1, you want "A:A"
Hence, if A1 =26, you want "Z:Z"
Hence, if A1=27, you want "AA:AA" and so on.
The formula to derive the column range would be

=SUBSTITUTE(ADDRESS(1,A1,4)&":"&ADDRESS(1,A1,4),1,"")

5.12 Find the value of first non blank cell in a range

=IFERROR(INDEX(A1:A10,MATCH(TRUE,INDEX(NOT(ISBLANK(A1:A10)),,),0)),"").

5.13 Find first numeric value in a range

=IFERROR(INDEX(A1:A100,MATCH(1,INDEX(--ISNUMBER(A1:A100),,),0)),””)

5.14 Find last numeric value in a range

=IFERROR(1/LOOKUP(2,1/A1:A100),””)

5.15 Find first non numeric value in a range

=IFERROR(INDEX(A1:A100,MATCH(1,INDEX(--ISTEXT(A1:A100),,),0)),””)

5.16 Find last non numeric value in a range

=IFERROR(LOOKUP(REPT("z",255),A1:A100),””)

5.17 Find last used value in a range

= IFERROR(LOOKUP(2,1/(A1:A100<>""),A1:A100),””)

5.18 MAXIF

Note – Excel 2016 has introduced MAXIFS function Suppose you want to find the Maximum Sales for East Region i.e. MAXIF

=SUMPRODUCT(MAX((A2:A100="East")*(B2:B100)))
=AGGREGATE(14,6,($A$2:$A$100="East")*($B$2:$B$100),1)

SUMPRODUCT formula is faster than the second formula.

5.19. MINIF

Note – Excel 2016 has introduced MINIFS function
Suppose you want to find the Minimum Sales for West Region i.e. MINIF

=AGGREGATE(15,6,1/($A$2:$A$10="West")*($B$2:$B$10),1)

But the above formula will not ignore blanks or 0 values in your range. If you want to ignore 0 values /blanks, in your range, then use following formula

=AGGREGATE(15,6,1/(($A$2:$A$10="West")*($B$2:$B$10<>0))*($B$2:$B$10),1)