DEV Community

loading...

Excel Formulas to Sum if Cells Contain Either X or Y!!

excelgeek profile image Andrews Originally published at geekexcel.com on ・3 min read

Here, we will explain the formulas to sum the values in the cells that contain either one text string or another (i.e. cells contain either X or Y) in Excel Office 365. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Formulas to sum if cells contain either X or Y
Formulas to sum if cells contain either X or Y

General Formula:

  • Use the below formula to sum the values in the cells that contain either one or another text string.

=SUMPRODUCT(–((ISNUMBER(SEARCH(“X”,range1)) + ISNUMBER(SEARCH(“Y”,range1)))>0),range2)

Syntax Explanations:

  • SUMPRODUCT – This function helps to multiply the corresponding arrays or ranges and returns the sum of the products. Read more on the SUMPRODUCT Function.
  • ISNUMBER – In Excel, the ISNUMBER Function returns TRUE when a cell contains a number, and FALSE if there will not be a number.
  • SEARCH – It will help to locate the character between ** two text string** and returns to the ** number** of the ** starting position** of the ** first text string** from the first character of the second text string. Read more on the SEARCH Function.
  • Plus operator (+) – This symbol is used to add the values.
  • Comma symbol (,) – It is a separator which helps to separate a list of values.
  • Parenthesis () – The main purpose of this symbol is to group the elements.
  • Minus Operator (-) – This symbol will help to subtract any two values.
  • Text (X or Y) – It is the criteria that are used to sum the cells.
  • Range – It represents the input values given in a worksheet.

Example:

  • Let’s consider the below image to sum the values in the cells that contain either one text string or another.
  • First, we will give the input values in Column B and Column C.
  • Then, enter the given formula in the formula bar section.
  • Finally, we will get the result in Cell F3.

Example
Example

Closure:

In this tutorial, we guided you to learn the simple formula used to sum the cells that contain either X or Y in Excel Office 365. Hope you like this article. Don’t forget to share your worthwhile feedback in the below section. Thank you so much for Reading!! Keep learning on Geek Excel!! *and Excel Formulas *!!

Read Also:

Discussion

pic
Editor guide