30
136
Chapter 8: Summing and Counting
Author: Joseph Rubin, CPA www.exceltip.com
The AND formula
Count the number of cells in the column Customer Name = Intel with
the criterion USA in the Market column. The result of the calculation is 2.
The formula is ={SUM((Market=”USA”)*(Customer_Name=”Intel”))}.
The * symbol in the array formula returns a result equal to the AND
formula. Here is a shortcut for inserting an array formula after entering
the formula into the cell: select the cell, press F2 (Edit) and press
Ctrl+Shift+Enter.
The OR formula
Replace the * sign with the + sign in the formula. The result of the
calculation is 18 five cells in the column named Customer Name, and
thirteen cells in the column named Market. The + sign in the array
formula returns a result equal to the OR formula.
You can use the COUNTIF formula instead of the array formula:
=COUNTIF (Market, “USA”)+COUNTIF(Customer_Name, “Intel”).
Comparing and Merging Lists, the
COUNTIF Formula
The ability to compare two lists helps you to pinpoint the similarities and
the differences between the lists. You can identify a name or item that
appears in one list and not the other, and names or items that appear in
both lists.
See more about comparing lists in Chapter 21, Consolidating Data.
A sample list: employees, inventory items, clients, suppliers and/or list of
account names in the accounting system, and more. In the example,
compare two lists of employees. In the following figure, notice that
Column A contains one list of employees and Column E contains the
other.
