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

Data Analysis

  • Move chart to a separate worksheet in Excel
  • How to Create Area Chart in Excel
  • Get column name from index in Excel Table
  • How to Create Thermometer Chart in Excel
  • Conditional Formatting Data bars Examples in Excel

References

  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • Perform case-sensitive Lookup in Excel
  • How to get address of last cell in range in Excel
  • Create hyperlink with VLOOKUP in Excel
  • Offset in Excel

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation no punctuation
  • Excel Data validation exists in list
  • Excel Data validation require unique number
  • Excel Data validation must contain specific text

Example of Count with Or Criteria in Excel

by

How to count data based upon multiple criteria?

Counting with Or criteria in Excel can be tricky. This article shows several easy to follow examples.

1. We start simple. For example, we want to count the number of cells that contain Google or Facebook (one column).

2a. However, if we want to count the number of rows that contain Google or Stanford (two columns), we cannot simply use the COUNTIF function twice (see the picture below). Rows that contain Google and Stanford are counted twice, but they should only be counted once. 4 is the answer we are looking for.

Worked Example:   How to Sum Range with Errors in Excel

2b. What we need is an array formula. We use the IF function to check if Google or Stanford occurs.

Explanation: TRUE = 1, FALSE = 0. For row 1, the IF function evaluates to IF(TRUE+TRUE,1,0), IF(2,1,0), 1. So the first row will be counted. For row 2, the IF function evaluates to IF(FALSE+FALSE,1,0), IF(0,1,0), 0. So the second row will not be counted. For row 3, the IF function evaluates to IF(FALSE+TRUE,1,0), IF(1,1,0), 1. So the third row will be counted, etc.

Worked Example:   Get work hours between dates and times in Excel

2c. All we need is a SUM function that counts these 1’s. To achieve this (don’t be overwhelmed), we add the SUM function and replace A1 with A1:A8 and B1 with B1:B8.

2d. Finish by pressing CTRL + SHIFT + ENTER.

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.

Worked Example:   Add line break based on OS in Excel

Explanation: The range (array constant) created by the IF function is stored in Excel’s memory, not in a range. The array constant looks as follows:

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

This array constant is used as an argument for the SUM function, giving a result of 4.

3. We can go one step further. For example, we want to count the number of rows that contain (Google and Stanford) or Columbia.

Post navigation

Previous Post:

How to Create Area Chart in Excel

Next Post:

Excel Line Chart

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

  • OR function Examples in Excel
  • Complete List of Excel Logical Functions, References and Examples
  • How to use IFS function in Excel
  • SWITCH function example in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to calculate nth day of year in Excel
  • How to join date and text together in Excel
  • Calculate date overlap in days in Excel
  • Add days to date in Excel
  • Add decimal hours to time in Excel

Grouping

  • Group times into unequal buckets in Excel
  • How to randomly assign people to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • If cell contains one of many things in Excel
  • Group numbers at uneven intervals in Excel

General

  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • How to Delete Cells, Row and Rows in Excel
  • Mark Workbook as Final in Excel
  • Flash Fill in Excel
  • Share Excel data with Word documents
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning