Kiến thức

# COUNT() COUNTA() COUNTBLANK() COUNTIF() COUNTIFS()

The questions…
How can I count the number of values?
Why won’t Excel count the cells with words in them?
Can I count the blank cells in a range?
Can I count a the number of cells that equal a condition?
How do I count cells that meet multiple conditions?

If you need to count the number of cells for any of the scenarios above Excel has a formula for that.  Most are quite basic and just work off of the formula and a set range. I will run through some examples using the phone data below, the is the table used before showing phone calls and who they were to, I have added a few extra rows in with no call detail as unanswered calls. Formulas…
Counting the number of values…
=COUNT(The range you want to count)

=COUNT(B3:B21) As shown above the formula returns the number of calls in the range specified. I then copied the formula into the adjacent column but it returned zero. This is due to COUNT() only recognising numbers; it will not COUNT any cells containing text. At this point it is worth mentioning that Excel views dates as numbers – although they are displayed in date form, that is formatting, the data is stored as a meaningless (to almost everyone) number. COUNTA returns cells containing text.

Counting the number of cells containing text…
=COUNTA(The range you want to count)

=COUNTA(C3:C21) Using COUNTA in the Type column now returns 19. If you are using a COUNT formula and the result is zero there is a good chance you are using the wrong one – as a further complication, that row containing the phone numbers, because they have a space after the area code Excel sees them as text rather than numeric values, so you would also need COUNTA rather than COUNT.

Count the number of blank / empty cells…
=COUNTBLANK(The range you want to count)

=COUNTBLANK(C3:C21) That is that table complete, although as usual not all of the information would be used in the real world and you really should have titles on the columns / rows, especially as with count of empty cells to show the number of missed calls – it is a safe assumption that the other values are totals.

Next, I want to count the number of calls made by each person, so I will add another column to the Extensions table.

Count the number of cells that meet a specified condition..
=COUNTIF(The range you want to count, The condition you want to match)

The range you want to count, in this instance the column containing the name, I have added the \$ to the range reference to fix this for copying…
=COUNTIF(\$G\$3:\$G\$21,

The condition you want to match, I could enter the name “Omar”, but instead I will reference the cell in the Extensions table that contains the name…
=COUNTIF(\$G\$3:\$G\$21,J3) The example above compares two text values, this is not case sensitive, eg, “Omar” would match “omar”. It can also match numeric values, either a value match, or a condition – as an example, if I wanted to count extension numbers below 130 I could use COUNTIF(Range,”<130″). I am not sure why, but with conditions such as less than Excel needs it in speech marks.

The above tells us the total number of calls made and received for each person, but what if you wanted to separate incoming and outgoing calls? COUNTIFS allows you to specific multiple conditions.

=COUNTIFS(The range you want to count, The condition you want to match, The next range you want to count, The next condition you want to match, Repeat…)

The range you want to count, the column containing the name…
=COUNTIFS(\$G\$3:\$G\$21,

The condition you want to match, the cell containing the name…
=COUNTIFS(\$G\$3:\$G\$21,J3,

The next range you want to count, the column containing the Type…
=COUNTIFS(\$G\$3:\$G\$21,J3,\$C\$3:\$C\$21,

The next condition you want to match, in this instance we need to match “Incoming”…
=COUNTIFS(\$G\$3:\$G\$21,J3,\$C\$3:\$C\$21,”Incoming”) Just copy the formula into the next column and change the second condition “Outgoing” and you’re done.

Tags:

COUNT

,

COUNTA

,

COUNTBLANK

,

COUNTIF

,

COUNTIFS

,

Excel

,

Formula

,

Microsoft

Chuyên mục: Kiến thức