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.