## Archive for 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 […]

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

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

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

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

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

Count the number of cells within a range that match multiple comma separated values

I received an email from one of my seven blog readers. In Excel, I have a column, say A, with some […]

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Count cells by cell and font color

Dave asks: How difficult would it be to make it count colour alone (so not unique values) and / or […]

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

Count multiple text strings in a cell range

The array formula below counts how many times multiple text strings exist in a cell range. It is easy to […]

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 the number of groupings 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 […]

Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]

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 how many numbers between a specific value occurring multiple times

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. Tip! I highly […]

Count unique distinct values in an Excel Pivot Table

ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]

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

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Count digits and ignore duplicates in any line

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 unique distinct values in a large dataset [UDF]

Overview This blog post describes how to count unique distinct values in list. We have created excel formulas before to […]

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

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

Count unique and duplicates text values in a closed workbook

Here is a picture of a closed workbook How to count unique distinct text values in a closed workbook This […]

Count unique and duplicate numbers in a closed workbook

Here is a picture of the closed workbook. There can´t be any blank cells or text values in your range. […]

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

Excel formula to 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 […]

Find positive and negative amounts that net to zero

I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]

Count unique values in two lists combined in excel

Unique values Unique values are values existing only once in a list or range. See picture below. Count unique values […]

Count matching values in one or more columns

In previous articles, I have talked about how to count unique/unique distinct/duplicates in a column/columns/range. In this article I count […]

Count date records between two dates in a range

In a previous article Count records between two dates in excel I counted dates that were between two dates in […]

Most common value between two dates in a range in excel

Array formula in D17: =INDEX(C3:G12, MAX(1*(IF(MAX(IF((B3:F12<=$D$15)*(B3:F12>=$D$14), COUNTIF(C3:G12, C3:G12), 0))=COUNTIF(C3:G12, C3:G12), (ROW(C3:G12)-MIN(ROW(C3:G12))+1), 0))), (MAX(IF(MAX(IF((B3:F12<=$D$15)*(B3:F12>=$D$14), COUNTIF(C3:G12, C3:G12), 0))=COUNTIF(C3:G12, C3:G12), (ROW(C3:G12)-MIN(ROW(C3:G12))+1)+((COLUMN(C3:G12)-MIN(COLUMN(C3:G12))+1)/16384), 0))-MAX(1*(IF(MAX(IF((B3:F12<=$D$15)*(B3:F12>=$D$14), COUNTIF(C3:G12, […]

Most common value between two dates in excel

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

Count unique distinct values based on date criteria in a range

Question: How do I count unique values (not date values) in a range where every second column contains dates? I […]

Count matching cell values in two columns

Question: How do I count common cell values in two columns? Answer: Array formula in G12: =SUM(IF(FREQUENCY(COUNTIF(List2, "<"&List2), COUNTIF(List2, "<"&List2))>0, […]

Count unique text values in a range containing both numerical and text values

Unique cell values are greyed, in the picture above. Array formula in D14: =SUM(IF(COUNTIF(tbl, tbl)=1, 1, 0)) + CTRL + […]

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

Count unique records between two dates

This blog article is one out of four articles on the same subject. Most common value between two dates in […]

Question: How do I count the number of unique months in excel? Answer: Array formula in C17: =SUM(IF(MATCH(DATE(YEAR(List), MONTH(List), 1), […]

Calculate frequency the past 90 days

Question: I have two columns of data... A1-A10 contain a list of dates. B1-B10 contain a value, AA, BB, CC, […]

Count unique values and unique distinct values in three ranges combined

I have written two blog posts about counting unique and unique distinct values in a range and two ranges combined. […]

Count unique values and unique distinct values in two ranges combined

Question: How do I count unique distinct values in two ranges combined? Answer: Formula in D14: =SUM(1/(COUNTIF(Table1, Table1)+COUNTIF(Table2, Table1)))+SUM(1/(COUNTIF(Table2, Table2)+COUNTIF(Table1, […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

Count unique and unique distinct values in a multicolumn range

Question: How do I count unique and unique distinct values in a range, for example cells A1:D4? Answer: Formula in […]

Count specific text string in a cell

Question: How do I count how many times a text string occurs in a cell in excel? Answer: Formula in […]

Count unique distinct values in two columns with date criteria

Question:I have a question about counting unique values from multiple ranges with multiple conditions. A1:A3 are filled with dates 2009,2008,2009 […]

Count unique distinct values in three columns combined

Question: How do I count unique distinct values in three different columns, not necessarily adjacent? Answer: Formula in B14: =SUM(IF(MATCH(List1, […]

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

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

How many common cell values are there in list 1 and 2?

Question: How many common cell values are there in list 1 and 2? Answer: Here is how to count matching […]

How to count unique combined column values

In a previous article Lookup Unique based on Multiple Conditions and Automatically filter unique row records from multiple columns, I […]

Count the number of times a specific number exists in a list

Question: How many times is the value 3 (B2) in this list (A:A)? Answer: =COUNT(SEARCH(B2,A:A)) + CTRL+ SHIFT + ENTER […]