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 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.

1. Below is 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 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.

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 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.

1. Input the text value *Liu in cell B4, and *MA* in cell C4.
2. Select cell B4 and C4.
3. Click the Formulas tab on 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.
   

   	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

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.