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 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 […]
The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]
The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]
The image above shows the COUNTBLANK function counting empty cells in cell range B3:B14. Note that the COUNTBLANK function ignores […]
Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]
The array formula in cell F3 counts cells in column B that contains at least one of the values in […]
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
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.