How To Count Cells Contains X Or Y, X And Y In Excel

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

1. How To Count Cells Contains X Or Y.

1.1 Method1: Use Multiple COUNTIF Function.

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

1.2 Method2: Use SUM & COUNTIF Functions.

1. Input the formula =SUM(COUNTIF(A2:A6,{“Tom*”,”Jackie Liu”})) in cell D3.
2. When you press the enter key, it will display the number 3 in cell D3.
3. Please note 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.

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

2. How To Count Cells Contains X And Y.

1. If you want to count cells that contain both value X and value Y, you can use the Excel COUNTIFS function in your formula.
2. 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],…)
   

3. 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.
   
4. 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.
5. 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.