Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Convert text string to valid reference in Excel using Indirect function
  • Two-way lookup with VLOOKUP in Excel
  • How to use Excel COLUMN Function
  • How to get last row in mixed data with blanks in Excel
  • Approximate match with multiple criteria in Excel

Data Analysis Examples

  • Excel Line Chart
  • How to Create Column Chart in Excel
  • How to create Checklist in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to create running total in an Excel Table

Data Validation Examples

  • Prevent invalid data entering in specific cells
  • Excel Data validation number multiple 100
  • Excel Data validation whole percentage only
  • Excel Data validation allow uppercase only
  • Excel Data validation must begin with

Count rows with multiple OR criteria in Excel

by

This tutorial shows how to Count rows with multiple OR criteria in Excel using the example below;

Formula

=SUMPRODUCT(--((criteria1)+(criteria2)>0))

Explanation

To count rows using multiple criteria across different columns – with OR logic – you can use the SUMPRODUCT function. In the example shown, the formula in H7 is:

=SUMPRODUCT(--((C5:C11="blue")+(D5:D11="dog")>0))

How this formula works

In the example shown, we want to count rows where the color is “blue”, OR the pet is “dog”.

Worked Example:   Check if multiple cells have same value with case sensitive in Excel

The SUMPRODUCT function works with arrays natively so for the first criteria, we use:

(C5:C11="blue")

This returns an array of TRUE FALSE values like this:

{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

For the second criteria, we use:

(D5:D11="dog")

Which returns:

{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}

These two arrays are then joined with addition (+), which automatically coerces the TRUE FALSE values to 1s and 0s to create an array like this:

Worked Example:   Get work hours between dates custom schedule in Excel

{2;0;1;1;1;0;1}

We can’t simply add these values up with SUMPRODUCT because that would double count rows with both “blue” and “dog”. So, we use “>0” together with the double negative (–) to force all values to either 1 or zero:

--({2;0;1;1;1;0;1}>0)

Which presents this array to SUMPRODUCT:

Worked Example:   Highlight duplicate rows in Excel

{1;0;1;1;1;0;1}

SUMPRODUCT then returns the sum of all elements.

Other logical tests

The example shown tests for simple equality, but you can replace those statements with other logical tests as needed. For example, to count rows where cells in column A contain “red” OR cells in column B contain “blue”, you could use a formula like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("red",A1:A10))+ISNUMBER(SEARCH("blue",B1:B10))>0))

Post navigation

Previous Post:

Count total matches in two ranges in Excel

Next Post:

Count occurrences in entire Excel Workbook

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
  • CODE function: Description, Usage, Syntax, Examples and Explanation
  • How to count specific characters in a cell in Excel
  • MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation
  • CONCAT function: Description, Usage, Syntax, Examples and Explanation
  • TEXT function: Description, Usage, Syntax, Examples and Explanation
  • Get work hours between dates in Excel
  • Generate series of dates by weekends in Excel
  • Get fiscal quarter from date in Excel
  • Get day name from date in Excel
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation
  • ISPMT function: Description, Usage, Syntax, Examples and Explanation
  • ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYS function: Description, Usage, Syntax, Examples and Explanation
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • Calculate loan interest in given year in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles