Count cells equal to any value in a list
The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the values in column D (Values2).
Formula in cell F9:
Explaining formula in cell F9
The COUNTIF function allows you to count cells in a range that are equal to a criterion. The great thing about the COUNTIF function is that it is possible to use criteria.
COUNTIF(B3:B18,D3:D9)
becomes
COUNTIF({"JX"; "UL"; "RS"; "XR"; "EJ"; "JX"; "SS"; "ZY"; "JX"; "QR"; "WF"; "PT"; "XG"; "UG"; "KZ"; "CF"}, {"WF"; "QR"; "SS"; "UL"; "JX"; "UG"; "CF"})
and returns the following array: {1; 1; 1; 1; 3; 1; 1}
The SUMPRODUCT function lets you sum the values in the array without the need to enter the fomula as an array formula.
SUMPRODUCT(COUNTIF(B3:B18,D3:D9))
becomes
SUMPRODUCT({1; 1; 1; 1; 3; 1; 1})
and returns 9 in cell F9. 1+1+1+1+3+1+1 = 9
Get Excel *.xlsx file
Count cells equal to a value in a list.xlsx
Count category
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
Question: How do I count the number of times a text string exists in a column? The text string may […]
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
