Basic, but one of the most useful formulae of Excel. The Sum is used for addition of one or more figures entered in different cells. Once the cell reference is decided by defining a range, we can insert values in the relevant cells to ultimately get their addition/sum as the result.
As per the name Count Function in Excel counts the number of cells with values inserted in them. A drawback for using this function is that it doesn’t count any other character than numbers. So, it would not consider text characters.
To counter the problem of Count Function, Count A was introduced. With Count A, Excel even considers text characters apart from just numbers. So if we insert values in the cells, the function counts the cells. One important thing about this function is that it counts only non-empty cells, whether it is text or number.
This formula actually counts the number of character in a cell. One thing that needs to be taken care of is that LEN even includes spaces while counting.
TRIM function eradicates all the extra spaces in a cell accept the single spaces between characters. It can be of extreme use if some data is transferred from a database, as it carries extra spaces which might create chaos when the data is compared by IF statement and VLOOKUP table.
Right, Left, Mid
The formulae mentioned above returns the quantified number of characters from a text string. The Right Function counts & returns the number of characters from the right. For instance in the text “Excel is Great” if we implement the formulae Right (A1, 6), then we will get the outcome as “Great”. The same application goes for the function Left and Mid.
Applied as VLOOKUP (lookup_value, table_array, col_index_num, range_lookup).
This is one of the most used formulae in Excel. Basically, this formulae assists in looking/searching for a precise information in the spreadsheet. For instance, the price for a specific article can be found from a list of similar or distinct articles with different prices. Let’s try to comprehend the same with and example. Supposedly we have to search for the price for a specific watch among an endless list of watches. We can put the relevant values in the formulae itself.
Lookup_value: This contains the name of the watch as entered in the spreadsheet.
Table_array: This contains the location of cells to be searched for. For e.g. A2:B20, which may contain the name & price for that watch.
Column_index_num: Contains the specific location denoted by a number where the information is searched for. If the prices are mentioned in the second column then, the column_index_num will be 2.
HLOOKUP : HLOOKUP (reference, array, row_number, row)
SUMIF, COUNTIF, AVERAGEIF
Formulas: =SUMIF (range, criteria, sum_range), =COUNTIF (range, criteria), =AVERAGEIF (range, criteria, average_range)
The aforementioned formulae are of great use in Excel and can simplify work to a significant extent. These formulae perform their pertinent functions i.e. SUM, COUNT, AVERAGE when a predefined condition(s) is met. That’s why they are implemented with IF function.
CONCATENATE : Concatenate (A1, ““, B1)
Utilizing above stated formulae can make using MS Excel much simpler and it can enhance the productivity to a new level. If after getting aware about these functions create an interest in you to know more about Excel and get profound with more such potential formulae and tricks on Excel, you always have an option to choose from some easy, interactive and significant e-learning courses offered by Naukri FastForward.