Lesson 111: Calculating conditional sum in Excel

SUMIF function in Excel will help you to sum cells based on the condition you want.

Join the channel Telegram of the AnonyViet 👉 Link 👈

How to calculate conditional sum in Excel

1. The SUMIF function below sums the values in the range A1:A5 that are less than or equal to 10.

2. The following SUMIF function gives the same result. The & operator combines the ‘less than or equal’ symbol.

3. The SUMIF function below sums the values in the range B1:B5 if the corresponding cells in the range A1:A5 contain the value 25.

4. The following SUMIF function gives the same result.

Text conditions

Use the SUMIF function in Excel to sum cells based on a text string that meets a condition. Always enclose text in quotes.

1. The SUMIF function below sums the values in the range B1:B5 if the corresponding cells in the range A1:A5 contain “circle”.

2. The SUMIF function below sums the values in the range B1:B5 if the corresponding cells in the range A1:A5 do not contain a “triangle”.

3. The SUMIF function below sums the values in the range B1:B5 if the corresponding cells in the range A1:A5 contain “circle” + 1 character. The question mark (?) represents a character.

4. The SUMIF function below sums the values in the range B1:B5 if the corresponding cells in the range A1:A5 contain a string + le. Asterisk

Lesson 111: Calculate conditional sum in Excel 28

Lesson 111: Calculating conditional sum in Excel 29

Date conditions

1. The SUMIF function below calculates the total sales after January 20, 2018.

Lesson 111: Calculating conditional sum in Excel 30

2. The SUMIF function below calculates today's total sales.

Lesson 111: Calculate conditional sum in Excel 31

3. The SUMIFS function below calculates the sales between two dates.

Lesson 111: Conditional summation in Excel 32

Note: the Excel SUMIFS function sums cells based on two or more conditions.

AND . condition

1. Sum the cells that meet the following conditions: Google and Stanford.

Lesson 111: Conditional summation in Excel 33

Note: remember, when using the SUMIFS function, the first parameter is the range to sum, followed by two or more range/condition pairs.

OR . condition

1. The formula below sums the cells that meet the conditions: Google or Facebook.

Lesson 111: Calculate conditional sum in Excel 34

2. However, if you want to sum cells that meet the following criteria: Google or Stanford, you cannot simply use the SUMIF function twice (see figure below).

Lesson 111: Calculate conditional sum in Excel 35

Note: The above formula calculates row 1 2 times because it contains Google and Stanford. And this is not the answer I want.

3. The array formula below will do exactly what you want.

Lesson 111: Calculate conditional sum in Excel 36

Note: end the array formula by pressing CTRL + SHIFT + ENTER. Excel will add curly braces {}. In addition, you can also view many other excel articles

here.

The article achieved: 5/5 – (100 votes)