# excel Counting and Summing Techniques

A common task in Excel is conditional counting or summing. This section contains a number of
formula examples that deal with counting various items on a worksheet, based on single or multiple criteria. You can adapt these formulas to your own needs.
Excel 2007 introduced two new counting and summing functions that aren’t available
in previous versions (COUNTIFS and SUMIFS). Therefore, I present two versions of
some formulas: an Excel 2007 and later version and an array formula that works with
Figure 3-8 shows a simple worksheet to demonstrate the formulas that follow. The following
range names are defined:
Figure 3-8: This worksheet demonstrates some useful formulas for counting and summing.
h Month: A2:A10
h Region: B2:B10
h Sales: C2:C10
Counting formula examples
Table 3-3 contains formulas that demonstrate a variety of counting techniques.
Table 3-3: Counting Formula Examples
Formula Description
=COUNTIF(Region,”North”) Counts the number of rows in which Region = “North”
=COUNTIF(Sales,300) Counts the number of rows in which Sales = 300
=COUNTIF(Sales,”>300”) Counts the number of rows in which Sales > 300
=COUNTIF(Sales,”<>100”) Counts the number of rows in which Sales <> 100
=COUNTIF(Region,”?????”) Counts the number of rows in which Region contains
five letters
=COUNTIF(Region,”*h*”) Counts the number of rows in which Region contains the
letter H (not case-sensitive)
=COUNTIFS(Month,”Jan”,Sales,”>200”) Counts the number of rows in which Month = “Jan” and
Sales > 200 (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Sales>200))} An array formula that counts the number of rows in
which Month = “Jan” and Sales > 200
=COUNTIFS(Month,”Jan”,Region,”North”) Counts the number of rows in which Month = “Jan” and
Region = “North” (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Region=”North”))} An array formula that counts the number of rows in
which Month = “Jan” and Region = “North”
=COUNTIFS(Month,”Jan”,Region,”North”)+
COUNTIFS(Month,”Jan”,Region,”South”)
Counts the number of rows in which Month = “Jan” and
Region = “North” or “South” (Excel 2007 and later)
{=SUM((Month=”Jan”)*((Region=”North”)+
(Region=”South”)))}
An array formula that counts the number of rows in
which Month = “Jan” and Region = “North” or “South”
=COUNTIFS(Sales,”>=300”,Sales,”<=400”) Counts the number of rows in which Sales is between 300 and 400 (Excel 2007 and later) {=SUM((Sales>=300)*(Sales<=400))} An array formula that counts the number of rows in which Sales is between 300 and 400 Summing formula examples Table 3-4 shows a number of formula examples that demonstrate a variety of summing techniques. Table 3-4: Summing Formula Examples Formula Description =SUMIF(Sales,”>200”) Sum of all Sales over 200
=SUMIF(Month,”Jan”,Sales) Sum of Sales in which Month = “Jan”
=SUMIF(Month,”Jan”,Sales)+SUMIF(Month,”Feb”,Sales) Sum of Sales in which Month =”Jan” or “Feb”

Chapter 3: Formula Tricks and Techniques 71
Formula Description
{=SUM((Month=”Jan”)*(Region=”North”)*Sales)} Sum of Sales in which Month=”Jan” and
Region=”North”
=SUMIFS(Sales,Month,”Jan”,Region,”North”) Sum of Sales in which Month=”Jan” and
Region=”North” (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Region=”North”)*Sales)} An array formula that returns the sum of Sales
in which Month=”Jan” and Region=”North”
=SUMIFS(Sales,Month,”Jan”,Region,”<>North”) Sum of Sales in which Month=”Jan” and Region
<> “North” (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Region<>”North”)*Sales)} An array formula that returns the sum of Sales
in which Month=”Jan” and Region <> “North”
=SUMIFS(Sales,Month,”Jan”,Sales,”>=200”) Sum of Sales in which Month=”Jan” and
Sales>=200 (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Sales>=200)*(Sales))} An array formula that returns the sum of Sales
in which Month=”Jan” and Sales>=200