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

Data Analysis

  • Randomize/ Shuffle List in Excel
  • Remove Duplicates Example in Excel
  • How To Compare Two Lists in Excel
  • Working With Tables in Excel
  • How to Create One and Two Variable Data Tables in Excel

References

  • Approximate match with multiple criteria in Excel
  • Basic INDEX MATCH approximate in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • To count total rows in a range in Excel
  • How to get last column number in range in Excel

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation allow uppercase only
  • Excel Data validation date in specific year
  • Excel Data validation require unique number
  • Excel Data validation must contain specific text

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:   Count day of week between dates 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 cells equal to case sensitive 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:   3D SUMIF for multiple worksheets 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:

44 Practical Excel IF function Examples

Next Post:

Large with criteria 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

  • SWITCH function example in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Generate series of dates by weekends in Excel
  • Get days, months, and years between dates in Excel
  • Get month name from date in Excel
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • Convert Unix time stamp to Excel date

Grouping

  • Group times into 3 hour buckets in Excel
  • If cell contains one of many things in Excel
  • How to randomly assign people to groups in Excel
  • Map text to numbers in Excel
  • Group arbitrary text values in Excel

General

  • Basic error trapping example in Excel
  • Excel Default Templates
  • How to generate random date between two dates in Excel
  • How to set or clear a print area in Excel Worksheet
  • Subtotal invoices by age in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning