# Count cells containing text from list

### Table of Contents

## 1. Count cells containing text from list

The array formula in cell F3 counts cells in column B that contains at least one of the values in D3:D5. Each cell is only counted once.

To enter the array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining the formula

The TRANSPOSE function changes the values in D3:D5 from being vertically arranged to being horizontally arranged.

TRANSPOSE(D3:D5)

Note the semicolon and comma characters that separate the values below.

{"B";"L";"O"} => {"B","L","O"}

The SEARCH function requires the values to be arranged in one column in the first argument and in one row in the second argument or vice versa.

That is why the TRANSPOSE function is needed, you could, of course, enter the values horizontally on the worksheet to avoid the TRANSPOSE function.

SEARCH(TRANSPOSE(D3:D5), B3:B14) returns the following array, displayed in the picture below.

Example, B is found in character position 4 in text string N, B, L, F, I. Note that the SEARCH function returns a #VALUE error if nothing is found.

The ISNUMBER function returns TRUE or FALSE determined by a value is a number or not, it happily ignores errors.

ISNUMBER(SEARCH(TRANSPOSE(D3:D5), B3:B14)) returns the following array.

The MMULT function sums the values row by row and returns an array shown in the picture below.

MMULT(ISNUMBER(SEARCH(TRANSPOSE(D3:D5), B3:B14))*1, ROW(D3:D5)^0)

To be able to do that we must use this array as the second argument: {1;1;1} It is determined by the number of cells in the list, in this case, three. They must be 1 and arranged vertically.

That is why I built this formula that builds the array automatically: ROW(D3:D5)^0

The MMULT function can't work with boolean values so I multiply them all by 1 to convert them into 0 (zeros) or 1.

The next thing is to check if the values in the array are larger than 0 (zero).

MMULT(ISNUMBER(SEARCH(TRANSPOSE(D3:D5), B3:B14))*1, ROW(D3:D5)^0)>0

Lastly, the SUM function adds the numbers and returns a total in cell F3.

### Get Excel *.xlsx file

Count cells containing text from list.xlsx

Check out this article if you want to count all text strings found in a cell range, in other words, cells might be counted twice or more.

Recommended articles

This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]

## 2. Count entries based on date and time

My issue is that I get the date in this format:

7/23/2011 7:00:00 AM

I am trying to count how many entries are between date and time. So I have a shift that starts at 6:30:00 PM and leaves at 7:00:00 AM the next morning. I have tried to convert that to a value to no avail.

I have the cells formatted correctly and tried your other formula =SUM(IF(($A$2:$A$10$D$1),1,0)) + CTRL + SHIFT + ENTER but that returned all records.

I am starting to think that Excel (I'm using 2010) cannot differentiate between the date and time. Any ideas would be greatly appreciated.

**Answer:**

Formula in cell F6:

#### Explaining formula in cell F6

The COUNTIFS function was introduced in Excel 2007 and it works like the COUNTIF function except you may use multiple conditions at the same time.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Pair |
criteria_range |
criteria |
Text |

1 |
B3:B17 | "<="&F3 | Are dates in B3:B17 smaller than end date in cell F3? |

2 |
B3:B17 | ">="&F2 | Are dates in B3:B17 larger than start date in cell F2? |

3 |
C3:C17 | F4 | Are Items in c3:C17 equal to cell F4? |

The ampersand character concatenates the logical operators <> and = to each cell or cell range before the COUNTIFS function evaluates the argument. If all conditions return TRUE then the record is counted as 1.

#### Step 1 - Criteria pair 1

The following image shows in column D date and time entries smaller than or equal to condition in cell F3, TRUE - Smaller, FALSE - larger.

#### Step 2 - Criteria pair 2

This image shows in column E date and time entries larger than or equal to condition in cell F2, TRUE - Smaller, FALSE - larger.

#### Step 3 - Criteria pair 3

This picture displays in column F items equal to condition in cell F4.

#### Step 4 - All conditions applied

This image shows which entries meet all conditions. If all conditions evaluate to TRUE then that specific record is counted, the formula returns 2 in cell F6 because two records meet all conditions.

Excel 2003 (and earlier versions) formula in cell F6:

### Count category

Table of Contents Count a specific text string in a cell (case sensitive) Count text string in a range (case […]

Table of Contents Count rows with data Count non-empty rows Count cells between two values Count cells based on a […]

Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]

### Count values category

This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

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