Those that contain FALSE evaluate to 0 (zero). TRUE and FALSE values for Sum_range are evaluated differently, which may cause unexpected results when they're added.Ĭells in Sum_range that contain TRUE evaluate to 1. The result is incorrect when Sum_range has TRUE or FALSE values. Make sure Criteria1,2 are in quotation marks if you are testing for text values, like a person's name. The result is 30.Ġ (Zero) is shown instead of the expected result. It then adds the numbers in Sum_range A2:A9 that meet both conditions. It excludes bananas by using in the Criteria1, "Bananas", and looks for the name "Tom" in Criteria_range2 C2:C9. =SUMIFS(A2:A9, B2:B9, "Bananas", C2:C9, "Tom")Īdds the number of products that aren’t bananas and are sold by Tom. It uses the wildcard character * in Criteria1, "=A*" to look for matching product names in Criteria_range1 B2:B9, and looks for the name "Tom" in Criteria_range2 C2:C9. =SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "Tom")Īdds the number of products that begin with A and were sold by Tom. In a new worksheet, right-click cell A1 and pick Match Destination Formatting under Paste Options. To use these examples in Excel, drag to select the data in the table, right-click the selection, and pick Copy.
![sumif function in excel sumif function in excel](https://www.exceltip.com/wp-content/uploads/2019/08/1-4.png)
You can enter up to 127 range/criteria pairs. For example, criteria can be entered as 32, ">32", B4, "apples", or "32".Īdditional ranges and their associated criteria. The criteria that defines which cells in Criteria_range1 will be added. Once items in the range are found, their corresponding values in Sum_range are added. The range that is tested using Criteria1.Ĭriteria_range1 and Criteria1 set up a search pair whereby a range is searched for specific criteria. SUMIFS(sum_range, criteria_range1, criteria1,. This video is part of a training course called Advanced IF functions.