Monday, May 2, 2011

Excel tricks that I fond

Below are collective tutorials that I like: Countif  & find

1) To count if it is apples or oranges

=countif(range, criteria)+countif(range, criteria)

2) To count more than "criteria".
=countif(range, ">"& criteria)

3) To count number of words ending with es
=countif(range, "*es")

4) To find text using formated text data with space 

5) To find text using vlookup & wildcard. *Note: Faster than SEARCH and returns the first value.
= vloopup("*"&target"*",Range,target column,0)
6) To find text using lookup & SEARCH.*Note: Slower than Vlookup and returns the last value
=lookup(2*15, Search(target,range),target column)

7) Dynamic Data extraction

No comments: