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(<condition-range>, <condition>, <sum-range>)

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

Condition

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", "=Something", 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

?

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.