Conditional Sum in Excel: Difference between revisions
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 | ==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.