|
|
|
|
|
|
Using SUMIF and COUNTIF
|
Q: |
I've got a spreadsheet of companies, what states
they're in, and sales amounts. How can I show just the SUM of sales
from all of the companies from a specific state (say NY) only?
|
|
|
|
|
A: |
You can use the SUMIF function to add up
only those values in a particular range that you want.
Here's a basic example. First I've got a list of numbers. I want to
add up the numbers, but ONLY those numbers that are over 40. Let's
say I want to count employee work hours, but only those hours for
employees who worked 40 hours or more.
|
|
|
=SUMIF(B1:B4,">=40")
|
|
|
|
|
|
You can see the SUMIF function takes at least two parameters. The first is the
range you're searching in for the "IF" part of the function. The second part is
the criteria, which can be a simple inequality like I used here, but it needs to
be enclosed in quotation marks. Here I've said, "SUM up the values from B1 to
B4, IF the value is greater than or equal to 40." If I press Enter, I get:
|
|
|
 |
|
|
|
|
|
I get 93 which is correct. Only Alice and Bill should have been added.
Here's an example that answers your question. This one adds up all of the sales, but only from
companies from NY:
|
|
|
=SUMIF(B1:B7,"NY",C1:C7) |
|
|
|
|
|
Press Enter I get 3500 which is correct:
|
|
|
 |
|
|
|
|
|
SUMIF can take a third parameter which is the range you want to add up. If you
don't specify it, SUMIF uses the first range. Basically this function adds up
all of the values from C1 to C7 where the corresponding entries in B1 to B7 are
equal to "NY". See how easy?
Now, there's also a COUNTIF function which is very similar. Instead of adding up
the values, however, it just counts them. Try this one:
|
|
|
=COUNTIF(A1:A6,"*American*") |
|
|
|
|
|
In this example I have a list of company names. I want to count all of the names
that include the phrase "American" in them. This COUNTIF function says count up
all of the entries from A1 to A6 where "American" is found. I included the
wildcard characters (the * asterisks) to say that any number of characters can
come before and after the search phrase - otherwise it would have looked for
just the word American by itself. Press Enter and you'll get 3.
See how easy COUNTIF and SUMIF are?
You can learn about these and many more functions just like them in my
Microsoft Excel video tutorials. |
|
|
|
|
|
|
By Richard Rost
Click here to sign up for more FREE tips
|
|
Check out these other pages that may be of interest to you: |
|
