Microsoft Office Tutorials and References
In Depth Information
Using Other Functions
COUNTIF and COUNTIFS
The COUNTIF and COUNTIFS functions work
similarly to SUMIF and SUMIFS, except that they
count the cells that meet the given condition,
rather than add them. Also, although COUNTIF
and COUNTIFS are Statistical functions, they are
similar to Logical functions so I describe them
here. To insert them into a worksheet, click the
More Functions button on the Formulas tab,
select Statistical, and then select either COUNTIF
or COUNTIFS from the menu that appears.
If you want to count all the potential clients in
the ZIP codes 78112 and 83046, you could use
the COUNTIFS function instead: =COUNTIFS
(G2:G200,“78112”, G2:G200,“83046”).
Using Text Functions
Not surprisingly, Text functions are used to
manipulate text. You use Text functions most
often in database worksheets—worksheets that
contain a list of names and addresses, inventory
items, or statistical data. These functions help
you manipulate the text to display what you want.
Text functions can also be used on numbers that
are entered as text such as a house number,
Social Security number, ZIP code, part number,
and such.
The syntax for the COUNTIF function is
=COUNTIF(RangeToCount,“Condition”).
The syntax for the COUNTIFS function is
=COUNTIFS(RangeToCount,“Condition”,RangeTo
Count2,“Condition2”,...and so on).
CONCATENATE
The CONCATENATE function takes the text in
several cells and combines it to form a new text
string. Take a look at the worksheet shown in
Figure 3-16.
Tip
Also like SUMIF, the Condition argument
in the COUNTIF and COUNTIFS functions
must be entered in quotations.
For example, suppose you have a list of potential
clients for your new printing business. You want
to properly staff your sales force so that they can
pay a visit to each these potential clients and
close that sale. To do that, you need to count the
number of clients in various parts of the city,
based on ZIP code. Assuming that the ZIP codes
are listed in the range G2:G200, you could type
=COUNTIF(G2:F200,“78112”) to count all the
potential clients in ZIP code 78112.
Figure 3-16
Use CONCATENATE to add text strings together.
 
Search JabSto ::




Custom Search