← Excel from Scratch: Formulas, Functions, and Data Analysis
Lesson
SUMIF and SUMIFS: sums with conditions
Sum values by one or several conditions without mixing up the argument order of the two functions.
SUMIF and SUMIFS
Summing by conditions: the key differences
The SUMIF function adds up the cells that meet one condition. Syntax: =SUMIF(range, criteria, [sum_range]). Important: the sum range is the third argument, and it's optional. If it's the same as the criteria range, you can leave it out. Example: =SUMIF(A2:A10, "Ivanov", B2:B10) — total sales (column B) only for the rows where the manager (column A) is “Ivanov”.
The SUMIFS function adds up the cells that meet several conditions. Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). The key difference: the sum range is the first argument, and it's required. This is a common mistake: beginners rearrange the arguments by analogy with SUMIF.
A real case: manager Ivanov's total sales for January. Formula: =SUMIFS(C2:C100, A2:A100, "Ivanov", B2:B100, "January"). Here C holds the amounts, A the managers, B the months. Criteria with text or comparisons go in quotation marks: "Ivanov", ">1000", "January".
Lesson notes
Summing by conditions: the key differences
The SUMIF function adds up the cells that meet one condition. Syntax: =SUMIF(range, criteria, [sum_range]). Important: the sum range is the third argument, and it's optional. If it's the same as the criteria range, you can leave it out. Example: =SUMIF(A2:A10, "Ivanov", B2:B10) — total sales (column B) only for the rows where the manager (column A) is “Ivanov”.
The SUMIFS function adds up the cells that meet several conditions. Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). The key difference: the sum range is the first argument, and it's required. This is a common mistake: beginners rearrange the arguments by analogy with SUMIF.
A real case: manager Ivanov's total sales for January. Formula: =SUMIFS(C2:C100, A2:A100, "Ivanov", B2:B100, "January"). Here C holds the amounts, A the managers, B the months. Criteria with text or comparisons go in quotation marks: "Ivanov", ">1000", "January".