Counting Unique Values in a Range

Nice and simple but effective, this formula counts how many unique items there are in a range.

Where Range A1:B10 contains the data

Sun	April
Mon	Mon
Tue	Tue
Wed	Wed
Thu	May
Fri	Fri
Sat	Sat
Sun	Sun
Mon	Mon
Jun	Tue

=SUMPRODUCT(($A$1:$B$11<>"")/COUNTIF($A$1:$B$11,$A$1:$B$11&"")) = 10

Check whether a cell contains a specific string

When working with folders, I find a handy formula to have is one that can identify a string within a larger string. For example the following data is used to determine whether the folder path was for office or factory staff and then route a save procedure accordingly:

Where A1 contains
London\Office\Bloggs, Joe

=IF(ISNUMBER(SEARCH("Office",A1)),"Office")  = Office

Where you have multiple locations that could appear, use a nested formula

Where A1 contains
London\Factory\Smith, Mary

=IF(ISNUMBER(SEARCH("Office",A2)),"Office",IF(ISNUMBER(SEARCH("Factory",A2)),"Factory")) = Factory