The LEFT and RIGHT functions request the same information. This can be used to extract parts of an address, URL, or reference for further analysis.

They will extract a specified number of characters from the start and end of text. The LEFT and RIGHT functions will do the opposing action of CONCATENATE. A space is entered for the text2 argument. In this example, CONCATENATE is used to combine the firstname and lastname into a fullname. It prompts you for the different values to use. This is useful for piecing together the different parts of text such as someone's name, an address, a reference number or a file path or URL. The CONCATENATE function combines the values from multiple cells into one.

The COUNTIFS function then has clean data and works correctly. In this example, the TRIM function is used in a separate column to clean the data in the region column ready for analysis. The TRIM function will prompt you for the text to remove spaces from. Users cannot see this space, which means it is not identified until something stops working. In this example, the COUNTIFS function from before is not working because a space has been accidentally used at the end of cell B6. This commonly occurs when content is pasted from somewhere else or when users accidentally type spaces at the end of text. The most common use of this function is to remove trailing spaces. This brilliant function will remove all spaces from a cell except the single spaces between words. This example uses both techniques in the same formula. When using the SUMIFS and COUNTIFS functions, the criteria must be entered as text or as a cell reference. In this example, we count the number of sales from the region entered into cell E3 that have a value of 200 or more. =COUNTIFS(criteria range 1, criteria 1, …) It, therefore, does not require a sum range like SUMIFS. The COUNTIFS function will count the number of values that meet specified criteria. And although not mentioned as part of the 12 most useful Excel functions for data analysis, there are also AVERAGEIFS, MAXIFS, and MINIFS functions. It is very similar to the SUMIFS function. The COUNTIFS function is another mega function for Excel data analysis. It is an extremely useful Excel function. It is definitely worth exploring the SUMIFS function in more detail. In this example, we are summing the values in column C for the region entered into cell E3. =SUMIFS(sum range, criteria range 1, criteria 1, …) The function asks you for the range of values to sum, and then each range to test and what criteria to test it for. So you can essentially ignore SUMIF as SUMIFS is a superior function. It sums values that meet specified criteria.Įxcel also has a function named SUMIF which does the same task except it can only test one condition, while SUMIFS can test many. SUMIFS is one of the most useful Excel functions. In this example, we have displayed the word “Yes” if the delivery date in column C is more than 7 days later than the order date in column B. =IF(logical test, value if true, value if false) The IF function asks you for the logical test to perform, what action to take if the test is true, and the alternative action if the result of the test is false. With IF, we could get Excel to perform a different calculation or display a different value dependent on the outcome of a logical test (a decision). This function means we can automate decision making in our spreadsheets.

Excel essentials for data analytics