Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: FREQUENCY function

FREQUENCY function: Description, Usage, Syntax, Examples and Explanation

What is FREQUENCY function in Excel? FREQUENCY function is one of Statistical functions in Microsoft Excel that calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be…

Count unique numeric values in a range in Excel

This tutorial shows how to Count unique numeric values in a range in Excel using the example below; Formula =SUM(–(FREQUENCY(data,data)>0)) Explanation To count unique numeric values in a range, you can use a formula based on the FREQUENCY and SUM functions. For example, assume you have a list of employee numbers together with hours worked on “Project X”, and you want…

Count unique text values in a range in Excel

This tutorial shows how to Count unique text values in a range in Excel using the example below; Formula =SUMPRODUCT(–(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0)) Explanation If you need to count unique text values in a range, you can use a formula that uses several functions: FREQUENCY , MATCH, ROW and SUMPRODUCT. It’s also possible to use COUNTIF, as explained below. Assume you have a…

Count unique numeric values with criteria in Excel

This tutorial shows how to Count unique numeric values with criteria in Excel using the example below; Formula {=SUM(–(FREQUENCY(IF(criteria,values),values)>0))} Explanation To count unique numeric values in a range with criteria you can use a formula based on the SUM and FREQUENCY functions, together with the IF function to apply criteria. For example, assume you have a list of employee numbers with hours…

Count unique text values with criteria

This tutorial shows how to Count unique text values with criteria using the example below; Formula {=SUM(–(FREQUENCY(IF(criteria,MATCH(values,values,0)),ROW(values)-ROW(valuesfirstcell)+1)>0))} Explanation To count unique values in a range with a criteria, you can use an array formula based on the FREQUENCY function. Assume you have a list of employee names together with hours worked on “Project X”, and you want know how many employees worked…

Excel Frequency Function Example

The FREQUENCY function in Excel calculates how often values occur within the ranges you specify in a bin table. You can also use the COUNTIFS function to create a frequency distribution. 1. First, enter the bin numbers (upper levels) in the range C4:C8. 2. Select the range D4:D9 (extra cell), enter the FREQUENCY function shown below and finish by pressing CTRL + SHIFT + ENTER.…