Let’s take a look. SUMIFS has three required arguments: sum_range, criteria_range1, and criteria1. After that you can enter additional range and criteria pairs to add additional conditions. In the first set of tables, we’re using the named range called “number” and a named range called “color.” I’ll enter the formulas in column H to handle the conditions in column F. To SUM all numbers equal to 15, I enter the range “number” for both the sum_range and criteria_range1 and I enter “15” for criteria1. When you use a number without an operator, there’s no need to use double quotes. To SUM all numbers with a color of blue, sum_range is “number”, criteria_range1 is “color”, and criteria is “blue.” In the next example, sum_range is “number”, but I need to enter two pair of range and criteria to satisfy both conditions. Excel returns “30”. The next example also requires two pair of range and criteria. One to test for numbers equal to 10, and the other to test for a color of blue. The final two problems involve summing numbers where the color is blue or purple. SUMIFS doesn’t support OR conditions directly, so the easiest way to handle this is to write two SUMIF formulas and simply add them together. As an alternative, you can enclose “blue” and “purple” in curly braces and feed them into the formula as criteria1. However, you’ll then need to wrap the entire SUMIFs function inside a SUM function. In the next set of tables, we’re using three named ranges: “date”, “product”, and “amount.” For the first problem, I enter the range “amount” for the sum_range, and the range “date” for criteria_range1. The criteria is “>1/1/2013”. In the next example, “amount” is again the sum_range. The criteria_range1 is the range “product” and the criteria is just “coffee” in double quotes. In the last example, “amount” is again the sum_range. However, I need to enter three additional range and criteria pairs to sum amounts where the product is “tea” and the date is within 2014. criteria_range1 is “product”, and criteria1 is “tea” criteria_range2 is “date”, and criteria2 is “>=1/1/2014” criteria_range3 is “date”, and criteria3 is entered as “<=12/31/2014” If you need to do a lot of conditional summing or counting with data, make sure you check out Pivot tables. Pivot tables make it really easy to generate both conditional sums and counts even with very large sets of data.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
