To work with the tool in Foresight Analytics Platform 10, use the new interface.

Sumif

The function wizard for the Sumif function looks as follows:

Syntax

Sumif(Range,Criteria[,SumRange])

Parameters

Range. The range of cells checked by the specified condition. The cells in range must contain numbers, text, names, arrays or links to numbers. Empty cells are skipped.

Criteria. The condition in the form of number, equation, cell reference, text or function determining summed cells. For example, the condition can be presented in the following form: 10, ">10", A1, "5", "Russia" or Today ().

NOTE. All text conditions and conditions with logical mathematical characters must be quoted with ("). It condition is number, quotation marks are not used.

SumRange. Optional attribute. The cells, which values are summed if they differ from the cells specified as a range. If the SumRange parameter is not specified, the cells specified in the Range parameter are used.

Description

It returns the sum of numbers in the specified cell range that satisfy the specified condition.

Comments

If it is required to specify one cell in the range, it should be specified as a range or using the Rng function. For example:

=Sumif(I8:I8,">0")

=Sumif(Rng(I8),">0")

If it is required to specify whole row or column in the range, it should be specified as a full range or using the Rng function. For example:

=Sumif(8:8,">0")

=Sumif(Rng(8:8),">0")

The SumRange parameter may differ in size from the Range parameter. On determining actual cells to sum up, the left upper cell of the SumRange argument is used as a start cell, then the cells of the range part that matches in size with the range argument are summed up. For example:

Range	Sum range	Actual cells
A1:A5	B1:B5	B1:B5
A1:A5	B1:B3	B1:B5
A1:B4	C1:D4	C1:D4
A1:B4	C1:C2	C1:D4

Example

Formula	Result	Description
=Sumif(A1:A3, ">160")	500	The cells from the A1-A3 range, which values are greater than 160, are summed up. This range contains the numbers 100, 200, 300.
=Sumif(A1:A3, 300,B1:B3)	21	The cells in the B1-B3 range are summed up if the corresponding cells in the A1-A3 range have the 300 value. The numbers located in the ranges: A1-A3: 100, 200, 300. B1-B3: 7, 14, 21.
=Sumif(A1:A3, ">160",B1:B3)	35	The cells in the B1-B3 range are summed if the corresponding cells in the A1-A3 range have the values greater than 160. The numbers located in the ranges: A1-A3: 100, 200, 300. B1-B3: 7, 14, 21.
=Sumif(A1:A3, "Russia",B1:B3)	28	The cells in the B1-B3 range are summed up if the corresponding cells in the A1-A3 range have the Russia value. The numbers located in the ranges: A1-A3: Russia, India, Russia. B1-B3: 7, 14, 21.