## Return value if it exists more then n times

Students are allowed to sign up to maximum two courses. How do I filter students who have signed up for more than 2 courses?

**Answer:**

**Array formula in B25:**

copied down as far as needed.

**Named ranges**

tbl (B3:D21)

What is named ranges?

### Download excel sample file for this tutorial.

Return multiple values if above frequency criterion.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROW(**reference**)** returns the rownumber of a reference

**COLUMN(**reference**)
**returns the column number of a reference

**ISTEXT(**value**)
**Checks whether a value is text, and returns TRUE or FALSE

**NOT(**logical**)**

Changes FALSE to TRUE, or TRUE to FALSE

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

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

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Count cells between a given value

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]

Count unique distinct values in two columns

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]

Filter unique distinct values, sorted and blanks removed

Table of Contents Filter unique distinct values, sorted and blanks removed Filter duplicate values, sorted and blanks removed EEK asks: […]

Count unique distinct text values in a range

Array formula in D14: =SUM(IF(FREQUENCY(COUNTIF(tbl,Â "<"&IF(ISTEXT(tbl), tbl, "")),Â COUNTIF(tbl,Â "<"&IF(ISTEXT(tbl), tbl, "")))>0,Â 1,Â 0)) + CTRL + SHIFT + ENTER […]

Create a unique distinct list where a corresponding column has text cell values

Question: I want a unique list to be created from a column where an adjacent column has text cell value? […]

Extract and sort text cells from a range containing both numerical and text values

Array formula in B16: =INDEX(tbl, MIN(IF(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1))=IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1)), INDEX(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), MIN(IF(SMALL(IF(ISTEXT(tbl), […]

### 2 Responses to “Return value if it exists more then n times”

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

I know something much easier:

Make one list out of all these lists and sort this list by name.

Or create a pivot table and count frequency by name.

Kind reagrds,

Pieter

Hey

Can you explain where the multiplication comes from (*167384)

regards Hans