Conditional Sum in Excel

Internal

• Microsoft Excel

Overview

Let's consider this dataset:

	A	B
1	Valisk	100
2	Amarisk	200
3	Valisk	300
4	Valisk	400
5	Amarisk	500

In order to sum the "B" values whose corresponding "A" cells are "Valisk", use:

=SUMIF(A1:A5, "=Valisk", B1:B5)

The sum criteria is in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. For example, criteria can be expressed as 32, ">32", B5, 32, "32", "apples", or TODAY(). __Important__ Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.

Examples:

!!Sum if the corresponding value is not empty

{{{ =SUMIF(A:A, "<>", B:B) }}}

!!Sum if the corresponding value is different from a given value {{{ =SUMIF(A:A, "<>Ovidiu", B:B) }}}

!!Sum if the corresponding is a date past 01/01/12. Won't sum if the value is empty cell or anything else than a date. {{{ =SUMIF(A:A, ">01/01/12", B:B) }}}

!! The value of the cell starts with JAS

?

!!The value of a cell includes a certain string

!!Sum based on the value of a cell

Do not use "=...", use:

{{{

    =SUMIF(B1:B10, H20, C1:C10)

}}}

This sums values from C1:C10 where the corresponding value from B1:B10 is equals to the value of the H20 cell.

__Referenced by:__\\ [{INSERT com.ecyrd.jspwiki.plugin.ReferringPagesPlugin WHERE max=20, maxwidth=50}]