Quick Info - COUNTIF And SUMIF
Have you ever wanted a quick count of cells or a quick sum? You can use the
status bar in Excel to display these and other calculations. See ‘Displaying
Simple Calculations’ in the Tips and Tricks section of I Get It! Development’s
web site to see how.
Sometimes you’d like to sum or count based on a condition. For this, you
need to the COUNTIF or SUMIF statements. The worksheet below contains many rows
of cities, states, and revenues.
We’d like to know how many of the cities we do business in are in
California, how many are in Missouri, and how many in Texas. The cells B76:C78
contain the state abbreviations and a simple formula
=COUNTIF($A$2:$C$72,B76)
The formula says to check the range from A2 to C72 for the contents of cell
B76. As the formula is filled down, it checks in turn for CA, MO, and TX. The
formula would have worked just the same if we had used the range $B$2:$B$72.
Below this table is another one showing revenues. Using SUMIF is a little
more complicated. You need to specify the rows to check for a condition, and the
rows to sum. Below you can see that we want to check column B, but sum column C.
The formula looks like:
=SUMIF($B$2:$C$72,B80,$C$2:$C$72)
This formula says to check the range that contains both the values to check
and the values to sum, that is, columns B and C. The value to check for is in
cell B80—in this case CA. The values to sum are now only in
column C as indicated by the last part of the formula.
Note that sometimes you will check and sum the same data. In this case the
sum range is not indicated:
=SUMIF($C$2:$C$72,"<1000")
Here we sum all of our sales that were under $1000.00.
Also note that we have used absolute references for the data to search. You
can see the absolute references because they have the dollar signs ($) in the
range. As you copy formulas from one cell to another, absolute references don’t
change. That way you can change what you’re looking for but not where
you’re looking for it.