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
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.
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 *!!
Discussion