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

Lookup and Reference Examples

  • How to use Excel LOOKUP Function
  • Count unique text values with criteria
  • How to get last row in mixed data with blanks in Excel
  • How to get last row in numeric data in Excel
  • Perform case-sensitive Lookup in Excel

Data Analysis Examples

  • How To Create Frequency Distribution in Excel
  • How to create dynamic reference table name in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Number and Text Filters Examples in Excel
  • How to create Checklist in Excel

Data Validation Examples

  • Excel Data validation specific characters only
  • Excel Data validation whole percentage only
  • Excel Data validation number multiple 100
  • Excel Data validation date in next 30 days
  • Excel Data validation allow uppercase only

Sum if cells contain either x or y in Excel

by

This tutorial shows how to Sum if cells contain either x or y in Excel using the example below;

Formula

=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",range1)) + ISNUMBER(SEARCH("rat",range1)))>0),range2)

Explanation

To sum if cells contain either one text string or another (i.e. contain “cat” or “rat”) you can use the SUMPRODUCT function.

Background

When you sum cells with “OR” criteria, you need to be careful not to double count when there is a possibility that both criteria will return true. In the example shown, we want to sum values in Column C when cells in column B contain either “cat” or “rat”. We can’t use SUMIFs with two criteria, because SUMIFS is based on AND logic. And if we try to use two SUMIFS (i.e. SUMIFS + SUMIFS) we will double count because there are cells that contain both “cat” and “rat”

Worked Example:   How to check if cell contains all of many things in Excel

Solution

One solution is to use  SUMPRODUCT with ISNUMBER + SEARCH or FIND. The formula in cell F4 is:

=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)

This formula is based the formula here that locates text inside of a cell:

ISNUMBER(SEARCH("abc",B4:B10)

When given a range of cells, this snippet will return an array of TRUE/FALSE values, one value for each cell the range. Since we are using this twice (once for “cat” and once for “rat”), we’ll get two arrays.

Worked Example:   Count numbers third digit equals 3 in Excel

Next, we add these arrays together (with +), which creates a new single array of numbers. Each number in this array is the result of adding the TRUE and FALSE values in the original two arrays together. In the example shown, the array looks like this:

Worked Example:   Count unique text values in a range in Excel

{2;0;2;1;0}

We need to add these numbers up, but we don’t want to double count. So we need to make sure any value greater than zero is just counted once. To do that, we force all values to TRUE or FALSE by checking the array with “>0”. This returns TRUE / FALSE:

{TRUE;FALSE;TRUE;TRUE;FALSE}

Which we then convert to 1 / 0 using a double negative (–):

{1;0;1;1;0}

Case-sensitive option

The SEARCH function ignores case. If you need a sensitive option, replace SEARCH with FIND.

Post navigation

Previous Post:

Sum if cells contain both x and y in Excel

Next Post:

Sum formulas only 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
  • How to create email address from name in Excel
  • Find function vs Search function in Excel
  • How to strip html from text or numbers in Excel
  • REPLACE, REPLACEB functions: Description, Usage, Syntax, Examples and Explanation
  • Remove leading and trailing spaces from text in one or more cells in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • Convert time to time zone in Excel
  • Calculate number of hours between two times in Excel
  • Sum race time splits in Excel
  • Get days, months, and years between dates in Excel
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • MIRR function: Description, Usage, Syntax, Examples and Explanation
  • FV function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARFR function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles