In this short tutorial, we will see the formulas used to count the matching rows with multiple OR criteria using the SUMPRODUCT Function in Excel Office 365. Let’s see them below!!
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 count the rows with multiple OR criteria.
=SUMPRODUCT(ISNUMBER(MATCH(range1,{“A”,”B”},0))*ISNUMBER(MATCH(range2,{“X”,”Y”},0)))
Syntax Explanations:
- SUMPRODUCT – In Excel, this function will multiply the corresponding arrays or ranges and returns the sum of the products. Read more on the SUMPRODUCT Function.
- ISNUMBER – It helps to return TRUE when a cell contains a number, and FALSE if there will not be a number. Read more on the ISNUMBER Function.
- MATCH – The MATCH functionin Excel will help to locate the position of a lookup value in a row, column, or table.
- 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.
- Range – It represents the input range from your worksheet.
- Criteria (A, B & X, Y) – It is the condition that is used to count the values.
- Multiplication (*) – In this symbol will multiply any two values or numbers.
Example:
- In the below screenshot, we will enter the input ranges in Column B and Column C.
- Then, enter the criteria in Column E and F.
- After that, apply the given formula in the formula bar section.
- Finally, we will get the result in Cell F7.
Wind-Up:
Here, we explained the formulas to count the rows with multiple OR criteria using the SUMPRODUCT Function in Excel Office 365. Hope that this article is useful to you. If you have any suggestions, feel free to share it with us. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *and Excel Formulas *!!
Top comments (0)