Conditional Sum in Excel: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
(Created page with "=Internal= * Microsoft Excel")
 
Line 1: Line 1:
=Internal=
=Internal=
* [[Microsoft Excel#Subjects|Microsoft Excel]]
* [[Microsoft Excel#Subjects|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}]

Revision as of 21:14, 29 December 2019

Internal

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}]