Welcome back for another Excel tutorial. Our topic today is the concatenate function. This is a very simple function but very useful. This is going to be short and sweet but this could make your life so much easier
There are two ways to use concatenate function in excel. 1st you could use =concatenate(cell 1,cell 2). We would use this formula to combine the First Name and Last name. Cell 1 would refer to A2 and Cell 2 is B2. If we use the formula =concatenate(A2,B2) we would come up with Lebron James. Please refer to the example below.
There is another way to do this. Instead of using the formula that I mentioned above, you can use =A3&B3. In the example below we would get the value of Cobe Bryant if you typed the formula on C3. You might be wondering why you need to use the concatenate function if you can use the amper sign version. Well, the concatenate version was introduced in the earlier version of Excel but the function is still available in the newer version.
So what can you use concatenate for? Imagine if you have a list of 1,000 contacts and the data that you need is the complete name. Instead of typing the first name and last in a new cell, you can use the formula to save time and effort.
In the next example, we would use the countif function with the concatenate function. We want to count the number of instances the term AugustApple is repeated in Column A. so first we need to type the =b2&c2 on cell A2. We then need to copy A2 to cell A3 to A8. Once we are done copying the formula in the cell we can start using the countif function.
On Cell J3 we would want to get the number of instances AugustApple is repeated. On K3 We would want to count the number of instances septemberapple is repeated. So let’s type =countif(A:A,J2&I3) on J3 to get the number of instances of Augustapple. Check out the screenshot below to get the information
Sumif and Averageif
You can use concatenate function with sumif, averageif and other functions like vlookup. In the screenshot below you can see how we can use the concatenate with sumif and averageif.
We will add all values in column D based on the criteria that we are looking for. On J4 we are looking to add specifically add all on column A marked as augustorange. Augustorange can be seen A3 and A7. on A3 the corresponding value in column D is 20 while on A7 the value on A7 is 21. if you add 20 and 21 that value would be 41. Another example is K3. We are looking for septemberapple. We can find this on A5 and A8 and have the value of 30 and 31 respectively. If we add them together we would see the value of 61 as seen on cell k3
We can use the same principle using averageif. Instead of adding the value of certain criteria, we will average them. In our first example, we added the value of Cell A3 and A7 and came up with the value of 41. If you divide that value by the number of instances you will get 20.5. That is the average of Cell A3 and A7.
Conclusion
Concatenate is a very simple yet useful function. Aside from the function that I mentioned, there is still a lot of function that you can use the concatenate function.
Thanks for dropping by. Stay tuned for more excel tutorials.