Sumif() Function

SUMIF is a function to sum cells that meet a single criteria. SUMIF can be used to sum cells based on dates, numbers, and text that match specific criteria.

SUMIF supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Syntax

The syntax for the SUMIF function in Microsoft Excel is:

SUMIF( range, criteria, [sum_range] )

Parameters or Arguments

range

The range of cells that you want to apply the criteria against. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

criteria

The criteria used to determine which cells to add. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.

sum_range

Optional. It is the range of cells to sum together. If this parameter is omitted, it uses range as the sum_range.

Excel Sumif Function Examples

Example 1

The following spreadsheet shows three examples of the Excel Sumif function used with text based critia.

For each call to the Excel Sumif function, the range argument (to be tested against the criteria) is either the cell range A2-A9 or the cell range B2-B9, and the [sum_range] argument (containing the values to be summed) is the cell range C2-C9.

Formulas:

	A	B	C
1	Month	Team	Sales
2	Jan	North 1	$36,693
3	Jan	North 2	$22,100
4	Jan	South 1	$53,321
5	Jan	South 2	$34,440
6	Feb	North 1	$29,889
7	Feb	North 2	$50,090
8	Feb	South 1	$32,080
9	Feb	South 2	$45,500
10
11	=SUMIF( A2:A9, "Feb", C2:C9 )
12	=SUMIF( B2:B9, "North 1", C2:C9 )
13	=SUMIF( B2:B9, "North*", C2:C9 )

Results:

	A	B	C
1	Month	Team	Sales
2	Jan	North 1	$36,693
3	Jan	North 2	$22,100
4	Jan	South 1	$53,321
5	Jan	South 2	$34,440
6	Feb	North 1	$29,889
7	Feb	North 2	$50,090
8	Feb	South 1	$32,080
9	Feb	South 2	$45,500
10
11	$157,559	- sum of cells C6-C9
12	$66,582	- sum of cells C2 & C6
13	$138,772	- sum of cells C2, C3, C6 & C7

Note that, in the example above:

-          The function in cell A13 uses the wildcard * and so finds cells in the range B2-B9 that begin with the text string "North". This is satisfied by the values "North 1" and "North 2".

-          In all three examples, the text based criteria (including the wildcard) are encased in quotes.

Example 2

The following example shows the Excel Sumif function using critia based on numeric values.

Formulas:

	A	B	C
1	1	200
2	2	45
3	1	550
4	2	450
5	1	20
6
7	=SUMIF( A1:A5, 1, B1:B5 )
8	=SUMIF(B1:B5, ">100" )

Results:

	A	B	C
1	1	200
2	2	45
3	1	550
4	2	450
5	1	20
6
7	770	- sum of cells B1, B3 & B5
8	1200	- sum of cells B1, B3 & B4

Note that, in the above spreadsheet:

-          The criteria in cell A8 is an expression, and so, is enclosed in quotes.

-          The [sum_range] argument is omitted from the function in cell A8 and so the values in the range array (i.e. cells B2-B6) are summed.

Notes

-          When sum_range is omitted, the cells in range will be summed.

-          Text criteria, or criteria that includes math symbols, must be enclosed in double quotation marks (").

-          Numeric criteria can be supplied without quotation marks.

-          The wildcard characters can be used in criteria.

-          The SUMIF function returns incorrect results when you use it to match strings longer than 255 characters or to the string #VALUE!.

-          The Excel Sumif function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be evaluated as equal.

-          The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using the upper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument. For example:

If range is	And sum_range is	Then the actual cells are
A1:A5	B1:B5	B1:B5
A1:A5	B1:B3	B1:B5
A1:B4	C1:D4	C1:D4
A1:B4	C1:C2	C1:D4

-          However, when the range and sum_range arguments in the SUMIF function do not contain the same number of cells, worksheet recalculation may take longer than expected.

Reference:

https://www.techonthenet.com/excel/formulas/sumif.php

https://exceljet.net/excel-functions/excel-sumif-function

https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b

http://www.excelfunctions.net/ExcelSumif.html

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501/