Count values


Excel » Count values »

Count a given pattern in a cell value

The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]

How to count overlapping time

The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]

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 […]

Count unique distinct values based on a condition

The following article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image […]

Count colored cells

I got a question about counting background colors in a cell range. Excel uses two different properties to color cells and […]

Count identical numbers in two columns but items must be in same row

Kidd asks: Hi Oscar,Need a formula to count identical numbers in two columns but items must be in same row (position). […]

Count overlapping days across multiple date ranges

NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, […]

Sort based on frequency row-wise

In this article I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The second example is a […]

Count overlapping days in multiple date ranges, part 2

In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

Count contiguous values

David asks: Hi Oscar, In column A, I have a long random list of two variables, "N/A" and the value […]

Count students

Kiishore asks Hi Oscar, I have the following issues. Any suggestions. Sheet 1 : Grade Male Female Primary Secondary High […]

Inventory consumption

Haroun asks: The problem is with regards to analysing the remaining life of my inventory based on the quantity available […]

Count unique distinct values in a filtered table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Count groups of each value

Joe asks: I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0" .These […]

Count a specific weekday in a date range

NETWORKDAYS function returns the number of whole workdays between two dates. The array formula in this blog post counts for […]

How many of a specific weekday falls between a start date and an end date except holidays

Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]

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 a large dataset with a date criterion

davidlim asks : i have ~100K rows, and Excel is literally stalled when running the formula. for the time being, […]

Count entries between date and time criteria

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

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 […]

Count unique distinct records with a date and column criteria

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

Count digits and ignore duplicates

Question: I have a question that I can’t seem to find an answer to: I want to make a full […]

Count cells based on a condition and month

Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Count cell blocks

Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if […]

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]

How to count unique distinct occurrences for each date

Question: How to count unique distinct values on the same date? Answer: Array formula in D3: =SUM(--(FREQUENCY(IF(B3=Date, COUNTIF(Item, "<"&Item), ""), […]

Count unique distinct numbers across multiple sheets (3D range

Count unique distinct numbers in a 3D range Array formula in E8: =SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0)) + CTRL + SHIFT + ENTER […]

Count cells between two values

Count cells between two values in a column Formula in D15: =ABS(MATCH(C12, A1:A10, 0)-MATCH(C13, A1:A10, 0))-1 Count cells between two […]

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 […]

Most frequent value between two dates

In this article I will show you how to extract the most frequent value (text or number) between two dates […]

Count unique distinct months

The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), […]

Count unique distinct values in two columns

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double click on cell C12 […]

Count unique distinct values

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