You are here: Home / Archives for Count

Count how often a value occurs – multiple criteria

October 20, 2008 By admin

In a previous post I covered counting how often a value occurs within a range. In that post we looked into one criteria, and we used the COUNTIF function.

But what if we want to count cells that meet multiple criteria. We can not use the COUNTIF function  in this case, so we will take a different approach and use the SUM function.

In the example below we want to count how often “John” appeared in the list in quarter 2 (Q2). i.e. our critera is “John” in column B and “Q2″ in column C. We will use the following formula for this purpose:

=SUM((B1:B17=”John”)*(C1:C17=”Q2″))

However this formula should be an “Array formula”. To make it an “Array formula” you should press CTRL+SHIFT+ENTER while you are in the edit mode of the formula. It should then look like this :

{=SUM((B1:B17=”John”)*(C1:C17=”Q2″))}

This formula will give us a result of 3 as you can see.

Filed Under: Data, Formulas, Functions Tagged With: Count, Occurs, Sum

Count how often a value occurs within a range

October 10, 2008 By admin

To count how often a value occurs within a range we use the COUNTIF function.

Because this function count cells based on a given criteria, that fact will help us limit the count to our specified value.

If in the example below we want to count how often “John” appeared in the list, then we will use the following formula:

=COUNTIF(B1:B15;”John”)

This will give us a result of 7 as you can see.

Read more on How to use the COUNTIF function.

Applies to Excel 2003

Filed Under: Data, Formulas, Functions Tagged With: Count, COUNTIF, Occurs

How to filter and count unique values among duplicates

October 2, 2008 By admin

If you have a column of data that contains duplicates, then you can use the advanced filter option to identify and copy the unique values to a different location. You can then easily count those values using COUNTA function or the ROWS function.

Perform the following steps to achieve this goal:

1. First make sure that your column has a header row.

2. Select your data range (including the header row).

3. On the Data menu point to Filter, then click Advanced Filter. The Advanced Filter window will open.

4. Click Copy to another location.

5. The List range text box should have your data range. If not then click on it, go to your worksheet and select the range.

6. Click on the Copy to text box. Go to your sheet and click on a blank column (or just a single cell) where you want your unique vales to be copied.

7. Select the Unique records only check box and click OK.

8. To count the unique values just copied, use the COUNTA function or the ROWS function. E.g if the unique values are in the range E1:E6 then in a separate cell enter one of the following formulas:

1. “=COUNTA(E2:E6)”

2. “=ROWS(E2:E6)”

Applies to Excel 2003

Filed Under: Data, Lists Tagged With: Autofilter, Count, Duplicates

Summary of Excel count functions

September 13, 2008 By admin

COUNT

Counts the number of cells that contain numbers within a given range. It can also accept a list of  arguments other than cell ranges and counts numbers within that list of arguments. Use COUNT to find out how many cells or arguments that have numeric values.

Syntax

COUNT(value1,value2,…)

Read more …

COUNTA

Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

Syntax

COUNTA(value1,value2,…)

Read more …

COUNTBLANK

Counts empty cells in a specified range of cells.

Syntax

COUNTBLANK(range)

Read more …

COUNTIF

Counts the number of cells within a range that meet the given criteria.

The criteria can be a number, text, expression or a cell reference.

Syntax

COUNTIF(range,criteria)

Read more …

Applies to Excel 2003

Filed Under: Formulas, Functions Tagged With: Count, COUNTA, COUNTBLANK, COUNTIF, Functions

How to count cells that contain numbers or dates

August 24, 2008 By admin

To count cells, within a given range or list of arguments, that contains only numbers you use the COUNT function.

  • Dates and text that represent numbers are considered numbers and will therefore be counted.
  • Logical values, text, or error values are not counted

Syntax

COUNT(value1,value2,…)

Examples:

COUNT(A1:A20) :counts numbers in a range of cells.
COUNT(A1:A20, 7) :counts numbers in a range of cells plus the second argument (7) which is counted as 1.

In the simple example depicted by the image below, cell B7 contains the function: =COUNT(B1:B6). The result is 4. as you can see the cells that contain the numbers 1, 2 and 17 and the cell that contains the date are counted. The the cells that contain the text “ABC” and “XYZ” are ignored.

Applies to: Excel 2003

Filed Under: Data, Date and Time, Functions Tagged With: Count, Data, Functions