Home > Topics > excel > SUMIFS between two values in Excel and Google Sheets

SUMIFS between two values in Excel and Google Sheets

Joseph Gordon-Levitt
Release: 2025-03-26 11:16:09
Original
596 people have browsed it

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, "

SUMIFS between two values in Excel and Google Sheets

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 between two values in Excel and Google Sheets

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)

SUMIFS between two values in Excel and Google Sheets

Google Sheets SUMIFS:

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

SUMIFS between two values in Excel and Google Sheets

SUMIFS between two values in Excel and 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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template