Formula to count cells on multiple criteria using OR logic

This formula will help you count cells on multiple criteria using OR logic by use the COUNTIFS function with an array constant.

Suppose you have a table:

A B
Student Name Course
1 Bob Jone Math
2 Ayako Tanaka Math
3 Po Quant Chemistry
4 Sue bryce Literary
5 andy samberg Chemistry
6 Tan chang Math
7 bob marley Literary
8 renee zellweger Physical

Now you want to count how many student learn math, physical and chemistry.

This formula will solve that: =SUM(COUNTIFS(B1:B8,{"Math","Physical","Chemistry"}))

The result is: 6

The general formula: =SUM(COUNTIFS(range,{"criteria1","criteria2","criteria..."}))

By default, the COUNTIFS function applies AND logic. Therefore we need to supply multiple criteria in an array constant: {"criteria1","criteria2","criteria..."} This array as criteria.