Search
  • Aman Jain

Basic Excel Functions you should know #2 (SUMIF | CONCAT)

Updated: 2 days ago

Hello Future #KeyboardWarriors, welcome to the second edition of the Basic Excel Functions series. I hope you have gone through the first post, in case you haven't; here's the link


So, today we will learn two simple formulas - SUMIF and CONCATENATE. I have been using these two formulas on almost all of my models for a variety of reasons and they never fail to disappoint. Let's get right to it.

SUMIF

The SumIF function adds all the numbers in a given range based on some criteria (eg, greater than 200)



Criteria can be applied to dates, numbers, and text. The SUMIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.



Formula – “=SUMIF(range,criteria,[sum_range])


In simpler words the formula is “=SUMIF(the range of cells you want to apply the criteria, criteria used to determine which cells to add, [optional]*, the cells to add)

*sum-range can be ignored if the range on which the criteria is tested is the same range from where you want to add


It's all easier when we see an example: (Link to the file so that you can work along)

Here, we have a Dataset from cell A2:C15

  • In column A, we have the Employee IDs

  • In column B, we have the base city these employees

  • In column C, we have the sales figure of the employees




Now, in cell, E3, we give an input which will be our criteria




In cell F2, we will use our SumIF formula to add up sales of the cities

Let’s have a look at what the formula does:

  • The range is B3:B15 which is the range of cities

  • The criteria is E3, which is Kolkata

  • The sum range is C3:C15, which is the range of the sales

How the formula works here is:

  • The formula searches for the criteria is the range, and adds up all the cells which satisfies the given criteria

  • In our case, since the range and sum range is different, the formula takes the adds the corresponding sum range which matches our criteria

  • So it looks for all the rows in column B where Kolkata is present, and then adds the corresponding cells in column C


Use of operators (>, <, <>, =)

We can also use operators for our criteria. Let’s say we want to add up all the numbers which is greater than or less than 100 in a given range of data. We need to use double quotes to define our operators. Here’s how we use the operators in our formula:


Formula = “SUMIF(C3:C15,”>30000”)”

If our criteria is in a cell then we can use Formula = “SUMIF(C3:C15,”>”&F2)


Let’s have a look:


Concatenate

The Concatenate function combines content of two or more cells into one. The function supports a maximum of 30 text items together.


Formula – “=CONCATENATE([text1],[text2],[text3]…)


In simpler words the formula is “=CONCATENATE(the first text value to join, the second text value to join, the third text value to join….)


Let's take an example:

Here, we have the first name in column A and last name in column B

In cell C2, we will use our concatenate formula to combine the two names


You may notice that the first and last names don't have a space in between them. That's because CONCATENATE will combine exactly what you tell it to combine.


If you want punctuation, spaces, or any other details to appear in the cell, you’ll need to modify the formula to include it. To add a space, we can simply add another argument: " " (two double quotes around a space). Make sure the three arguments are separated by commas:


Formula: =”CONCATENATE(A3,” “,B3)”

We can also add punctuation or anything we want in between. Let’s have a look:


0 views
 

©2020 by IndexMatched. Proudly created with Wix.com