This article will show you examples of how to count the number of cells which contains either value X or value Y. It will also show you examples of how to count cells contain both value X and value Y.

### 1. How To Count Cells Contains X Or Y.

#### 1.1 Method1: Use Multiple COUNTIF Function.

- Below is the example data cells of this article.
- The user name is input in column
**A**. - Column
**Name1**contains the first name text value, and column**Name2**contains the second name text value. - Input the formula
**=COUNTIF(A2:A6, B2) + COUNTIF(A2:A6, C2)**in cell**D2**. - When you press the enter key, it will display the number
**3**in cell**D2**. - This is because cell
**A2**Â and**A3**contains the value**jerry li**case insensitive, and cell**A4**contains the value**Tom Ma**.

**A****B****C****D****1****User Name****Name1****Name2****Count****2**Jerry Li Tom Ma jerry Li 3 **3**jerry li 3 **4**Tom Ma *Liu *MA* 2 **5**Tom Sun **6**Jackie Liu

#### 1.2 Method2: Use SUM & COUNTIF Functions.

- Input the formula
**=SUM(COUNTIF(A2:A6,{“Tom*”,”Jackie Liu”}))**in cell**D3**. - When you press the enter key, it will display the number
**3**in cell**D3**. - Please notes the above formula can only use text string, if you want to use cell references such as
**=SUM(COUNTIF(A2:A6,{B3,C3;}))**in the formula, then it will throw errors.

#### 1.3 Method3: Use SUMPRODUCT & COUNTIF Functions.

- Input the text value
***Liu**in cell**B4**, and***MA***in cell**C4**. - Select cell
**B4**and**C4**. - Click the
**Formulas**tab on excel top menu bar. - Then click the
**Define Name**menu item in the**Defined Name**group to open the**New Name**dialog. - Input the name
**test1**in the**Name**text box. - Click the
**OK**button to save the name range. - Input the formula
**=SUMPRODUCT(COUNTIF(A11:A15,test1))Â**in cell**D4**. - When you press the enter key, it will show the number
**2**in cell**D4**. - The matched cell is
**A4**( “Tom Ma”) and**A6**(“Jackie Liu”).

### 2. How To Count Cells Contains X And Y.

- If you want to count cells that contain both value X and value Y, you can use the excel
**COUNTIFS**function in your formula. - The excel function
**COUNTIFS**has the below syntax, you can pass multiple**cell_range, criteria pairs**to the function, then it will return the cell number that matches all the conditions. Please note all the parameters are separated by a comma(,).COUNTIFS(range1,criteria1,[range2],[criteria2],â€¦)

- Below are the example data cells. Column
**A**contains the**User Name**, column**B**contains the**Math Score**, andÂ column**C**contains the**English Score**.

**A****B****C****D****E****F****1****User Name****Math Score****English Score****Contains Both User Name, Math Score, English Score****2**Jerry Li 98 89 User Name *Tom* **3**jerry li 68 86 Math Score >80 **4**Tom Ma 76 67 English Score <90 **5**Tom Sun 82 65 **6**Jackie Liu 88 99 Count 1 - Column
**E**&**F**contains the criteria that columns**A**,**B**, and**C**should match, you can input different criteria values in column**F**to get different results in cell**F6**. - Input the formula
**=COUNTIFS(A2:A6,F2,B2:B6,F3,C2:C6,F4)**in cell**F6**and press the enter key, then you can see the cell number that matches both the criteria in cell**F6**.