As the name suggests SUMIF and SUMIFS formulae are formed by combining SUM and IF functions. In simple English, this implies that these functions can add items or cells that fulfill a particular criterion. Let's understand it in detail.
A cell reference in Excel refers to the value of a different cell or cell range on the current worksheet or a different worksheet within the spreadsheet. A cell reference can be used as a variable in a formula. The simplest cell reference appears as a simple mention of the referred cell after an equal sign. For example (=C5) refers to the value. Excel definition: If someone excels in something or excels at it, they are very good at doing it. Meaning, pronunciation, translations and examples.
Understanding SUMIF and SUMIFS Functions
You know SUM function has the capability to add items, and SUMIF function extends the capabilities of SUM function by giving you the choice to SUM only those items that meet a particular criterion.
At the same time, the SUMIFS function is a relatively newer function (introduced with Excel 2007) that extends the abilities of a SUMIF function by giving you the facility to add only those items that meet a set of criteria.
SUMIF Function in Excel
According to Microsoft Excel SUMIF is defined as a function that 'Adds the cells specified by a given condition or criteria'.
The Syntax of SUMIF Function is as under:
=SUMIF(range, criteria [, sum_range])
Here, ‘range
' refers to the cells that you want to be evaluated by the ‘criteria
'.
‘criteria
' refers to the condition that specifies which items are to be added. ‘criteria
' can be a number, expression, or a text string.
‘sum_range
' is an optional argument, it specifies the cells to be added. If ‘sum_range
' argument is omitted then SUMIF treats ‘range
' as ‘sum_range
'.
How to use SUMIF in Excel
Now let's try and understand how to apply a SUMIF formula.
Suppose we have a sales table as shown below:
Objective: Our objective is to find the Total Sales Amount earned from the North region.
Let's try to apply a SUMIF to find the solution.
range: In the ‘range
' argument select the cells that include your criteria.
criteria: In the ‘criteria
' argument enter your criteria i.e. 'North' Region. Please note that writing '=North' is the same as writing 'North'.
sum_range: In the ‘sum_range
' argument select the cells that should be added after evaluating the criteria.
After applying this formula the result comes out as 490688 which is the addition of D3, D7, and D10.
Few Examples of SUMIF
Let's have a look at some of the examples where SUMIF Function can be used:
Example 1: Suppose we have a table as below and now our goal is to find the total units sold in the East Region.
![What Is Excel Definition What Is Excel Definition](https://i.ytimg.com/vi/bTiJYig3w1Q/maxresdefault.jpg)
To find the solution we can apply the formula as:
This formula results into 129
Example 2: In the same table as above, write a formula to find the total amount earned before the date '01/01/2012'.
In this case, we can apply the formula as:
=SUMIF(A3:A11,'<01/01/2012',D3:D11)
Example 3: In this example, we have a table of performance of some schools at local events. Here our task is to find the total number of awards won by School C in all the events.
Here we can use a formula:
This formula results in 24 which is D5 + D7 + D9.
SUMIFS Function in Excel
In the above examples we have seen how to use the SUMIF function, now let's move to the SUMIFS function. As I have foretold that SUMIFS is an extension of SUMIF function, it has the ability to add the items from a range that satisfies multiple conditions.
According to Microsoft Excel SUMIFS can be defined as a function that 'Add the cells specified by a given set of conditions or criteria'.
The syntax of the SUMIFS function is as follows:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Here, ‘sum_range
' specifies the cells that are to be added based on the given conditions. It is a required field.
‘criteria_range1
' specifies the first range where the first criteria is to be evaluated.
‘criteria1
' specifies the condition that is to be evaluated in the ‘criteria_range1
'.
‘criteria_range2
', ‘criteria2
' specifies the other ranges and their respective conditions. It is an optional argument. SUMIFS supports a total of 127 range criteria pairs.
How to use SUMIFS in Excel
To understand how to use the SUMIFS function let's take an example:
Let's consider we have a Sales Table as shown below.
Objective: Our objective is to find the total units sold from the North region after the date of 31/12/2011.
So, we will try to apply the SUMIFS function as:
‘sum_range': In the ‘sum_range
' argument select the range whose items you want to add up.
‘criteria_range1': In the ‘criteria_range1
' argument select the range where the first condition should be evaluated. In this case, our first condition is based on the region.
Microsoft Excel Definitions For Common Terms | Windows Central
‘criteria1': In the ‘criteria1
' argument enter the condition that you wish to evaluate in ‘criteria_range1
'. In our case the ‘criteria_1
' is 'North'.
‘criteria_range2': In the ‘criteria_range2
' argument select the range where the second condition should be evaluated. In this example, our second condition would be based on the date.
‘criteria2': In the ‘criteria2
' argument enter the condition that you wish to evaluate in ‘criteria_range2
'. In our case the ‘criteria_2
' is '>31/12/2011'.
The output of this formula is 903, which is the sum of C1 and C7.
Few Examples of SUMIFS
Example 1: In this example, we again have a sales table, and our task is to find the total units sold by the salesperson 'Steve' in the year 2012.
We can apply a SUMIFS formula in this case as:
=SUMIFS(D3:D11,C3:C11,'Steve',A3:A11,'>31/12/2011')
What Is Excel Definition In Hindi
This formula gives the result of 907.
Example 2: In this example, we will try to find the total amount raised from the 'West' region where items sold are greater than 500.
In this case, we can use the formula:
Example 3: In this example, we have a daily measurements table as shown below. Here our task is to find the total Rainfall for all the days where humidity is greater than 65 and temperature is greater than 40.
To find the solution we can apply the formula:
=SUMIFS(B4:D4,B3:D3,'>40',B2:D2,'>65')
This gives 38 as the result which is a sum of B4 and D4.
Few Important points about SUMIF and SUMIFS Formulas
- Both SUMIF and SUMIFS formulae support wildcard characters. You can use wildcard characters (like: ‘*' and ‘?') in the ‘
criteria
' argument. - In SUMIF the cells in ‘
range
' argument and ‘sum_range
' need not be of the same shape and size. But this does not stand true in the case of SUMIFS. - You can use multiple operators (like: '=', '>', '<', '>=', '<=', '<>') in the ‘
criteria
' argument of both the functions.
So, this was all about Excel SUMIF and SUMIFS formulae. Feel free to drop your comments regarding the topic.
Cells, Rows, and Column Formatting
From the Home ribbon, you can format the font, number, alignment, border, pattern, and protection of your cells, rows, and columns.
You can also click on the Expand icon to open the traditional Format Cells dialog box that contains tabs for these various categories.
Worksheet/Page Setup
Just as the Home ribbon provides you with the options that used to be only accessible in the Format Cells dialog box, the Page Layout ribbon gives you access to features that used to be in the Page Setup dialog box (click on the Expand icon to access that traditional dialog box).
In the Page Setup group, you can set the margins, orientation, and print area. Note that the Print Titles icon evokes the Page Setup dialog box, and that Print Titles here refers to the row and column headings (e. g., A, B, C, 1, 2, 3, etc.) not the sheet's headers and footers. With the Page Setup dialog box open, you can click on the Header/Footer tab to change them. You can also access the headers and footers via the Insert ribbon.
You can change a column's width by putting your cursor between the letter for that column and the next. When you get the two-headed arrow, drag to the right or left as desired.
Resizing Columns and Rows
Double-clicking on that same spot will 'autofit' the column, making the column as wide as the largest piece of text/data in that column.
The same technique works for rows; put your cursor between the two numbers until you see the two-headed arrow, and then drag or double-click.
Hiding Rows and Columns
To hide a column or row, right-click on the letter or number for the column or row (A, B, C, etc. or 1, 2, 3, etc.) and select Hide.
To reveal a column or row, select the columns or rows before and after the missing column or row, right-click, and select Unhide.