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

**Contact Oscar**

You can contact me through this contact form