Conditional Sum in Excel: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
No edit summary
 
(3 intermediate revisions by the same user not shown)
Line 21: Line 21:


In order to sum the "B" values whose corresponding "A" cells are "Valisk", use:
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)
  =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().  
==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.
'''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.
Line 38: Line 42:
  =SUMIF(A:A, "<>Ovidiu", B:B)
  =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==
==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)
=SUMIF(A:A, ">01/01/12", B:B)


==The value of the cell starts with JAS==
==The value of the cell starts with JAS==

Latest revision as of 00:05, 19 September 2023

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(<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

?

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.