SUM Excel Range with Conditions

Use SUMIF, SUMIFS or SUMPRODUCT to SUM a range of cells Conditionally


=SUMIF(range, criteria, [sum_range])

The SUMIF function syntax has the following arguments:

  • range Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).

  • criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included - a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.


=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)


  • Sum_range (required) The range of cells to sum.

  • Criteria_range1 (required) The range that is tested using Criteria1 Criteria_range1 and Criteria1 set up a search pair whereby a range is searched for specific criteria. Once items in the range are found, their corresponding values in Sum_range are added.

  • Criteria1 (required) The criteria that defines which cells in Criteria_range1 will be added. For example, criteria can be entered as 32, ">32", B4, "apples", or "32".

  • Criteria_range2, criteria2, … (optional) Additional ranges and their associated criteria. You can enter up to 127 range/criteria pairs.


=SUMPRODUCT(array1, [array2], [array3], ...)


  • array1 (Required) The first array argument whose components you want to multiply and then add.

  • [array2], [array3],... (Optional) Array arguments 2 to 255 whose components you want to multiply and then add.



DOWNLOAD EXCERCISE FILES

COM-SUMIFS_VS_SUMPRODUCT-01
.xlsx
Download XLSX • 48KB
SUMIFS_VS_SUMPRODUCT-01
.xlsx
Download XLSX • 39KB



629 views0 comments