Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to count table columns in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • Example of COUNTIFS with variable table column in Excel
  • Conditional Formatting Rules in Excel
  • How to Create One and Two Variable Data Tables in Excel

References

  • Create hyperlink with VLOOKUP in Excel
  • How to get first row number in range in Excel
  • Count rows that contain specific values in Excel
  • How to get first column number in range in Excel
  • How to get address of named range in Excel

Data Validations

  • Excel Data validation require unique number
  • How To Create Drop-down List in Excel
  • Excel Data validation only dates between
  • Excel Data validation date in next 30 days
  • Excel Data validation date in specific year

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”

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.

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:

{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:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon 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

Logical Functions

  • OR function Examples in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Roll back weekday to Friday base on a particular date in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Excel Date & Time Functions Example
  • Add decimal minutes to time in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel
  • How to randomly assign people to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • How to randomly assign data to groups in Excel

General

  • Creating and Opening an existing file in Excel
  • AutoFit Column Width, AutoFit Row Height in Excel
  • Check if multiple cells have same value in Excel
  • Currency vs Accounting Format in Excel
  • Customize Ribbon In Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning