Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Last row number in range
  • Count rows with at least n matching values
  • How to get last row in text data in Excel
  • How to use Excel ROWS Function
  • Count rows that contain specific values in Excel

Data Analysis Examples

  • Chart Axes in Excel
  • How To Create Frequency Distribution in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Create Scatter Chart in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel

Data Validation Examples

  • Excel Data validation must begin with
  • Excel Data validation must contain specific text
  • Excel Data validation specific characters only
  • Excel Data validation date in next 30 days
  • Excel Data validation date in specific year

Sum if equal to one of many things in Excel

by

This tutorial shows how to Sum if equal to one of many things in Excel using the example below;

Formula

=SUMPRODUCT(SUMIF(range,things,values))

Explanation

If you need to sum values when cells are equal to one of many things, you can use a formula based on the SUMIF and SUMPRODUCT functions.

Worked Example:   Excel Data validation must not contain

In the example shown, the formula in H5 is:

=SUMPRODUCT(SUMIF(B5:B11,things,C5:C11))

How this formula works

The SUMIF function takes three arguments: range, criteria, and sum_range.

For range, we are using B5:B11. These cells contain the values we are testing against multiple criteria.

Worked Example:   Sum if ends with in Excel

For criteria, we are using the named range “things” (E5:E7). This range contains the 3 values we are using as criteria. This range can be expanded to include additional criteria as needed.

For  sum_range, we are using using C5:C11, which contains numeric values.

Worked Example:   Sum if date is greater than in Excel

Because we are giving SUMIF more than one criteria, it will return multiple results – one result for each value in “things. The results come back an array like this:

=SUMPRODUCT({60;30;12})

SUMPRODUCT then sums all items in the array and returns a final result, 102.

Post navigation

Previous Post:

Sum last n columns in Excel

Next Post:

Sum if date is greater than in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove text by variable position in a cell in Excel
  • Remove leading and trailing spaces from text in one or more cells in Excel
  • How to get page from URL in Excel
  • How to count total words in a cell in Excel
  • How to remove trailing slash from url in Excel
  • Calculate years between dates in Excel
  • Convert Excel time to Unix time in Excel
  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • How to determine year is a leap year in Excel
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARFR function: Description, Usage, Syntax, Examples and Explanation
  • RRI function: Description, Usage, Syntax, Examples and Explanation
  • VDB function: Description, Usage, Syntax, Examples and Explanation
  • ISPMT function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles