SUMIFS between two values in Excel and Google Sheets

This tutorial demonstrates how to sum data within a specific range using Excel and Google Sheets' SUMIF and SUMIFS functions. For large datasets, calculating totals based on a range (e.g., values greater than A and less than B) is common. SUMIFS efficiently handles this.

Excel SUMIFS for a Range:

The SUMIFS function in Excel (2007 and later) allows summing data based on multiple criteria.

• Inclusive Range: SUMIFS(values, values,">="&min_number, values, " This includes the minimum and maximum values in the sum.

• Exclusive Range: SUMIFS(values, values,">"&min_number, values, " This excludes the minimum and maximum values.

Example: To sum values in C2:C10 between 200 and 300 (inclusive): =SUMIFS(C2:C10, C2:C10, ">200", C2:C10, "

For greater flexibility, use cell references (e.g., F2 for minimum, F3 for maximum): =SUMIFS(C2:C10, C2:C10, ">"&F2, C2:C10, "

Excel SUMIF for a Range (Older Excel Versions):

In older Excel versions lacking SUMIFS, you can achieve similar results using two SUMIF functions:

SUMIF(values, ">"&min_number) - SUMIF(values, ">="&max_number)

Example: =SUMIF(C2:C10, ">200") - SUMIF(C2:C10, ">=300") (or with cell references: =SUMIF(C2:C10, ">"&F2) - SUMIF(C2:C10, ">="&F3))

SUMIFS with Additional Criteria:

SUMIFS supports multiple criteria pairs (up to 127). For instance, to sum Grapes sales between $200 and $300, assuming a column for item names (e.g., column B):

=SUMIFS(C2:C10, B2:B10, "Grapes", C2:C10, ">200", C2:C10, " (or with cell references for item name and range limits)

Google Sheets SUMIFS:

The SUMIFS formulas used in Excel work identically in Google Sheets.

This concludes the guide on summing data within ranges in Excel and Google Sheets. Practice workbooks are available (links omitted as they were not provided in the original text).

The above is the detailed content of SUMIFS between two values in Excel and Google Sheets. For more information, please follow other related articles on the PHP Chinese website!