How to use the COUNTIF and SUMIF functions in Excel — a quick introductory tutorial

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

I wanted to continue building on my popular series on and by discussing two additional and very useful functions called and . Surprisingly, they’re not as widely used as you’d expect, and sometimes knowing which to use is a bit confusing.

is a function that will count (as in 1, 2, 3…) the number of entries within a range if certain criteria are met, and the data within each entry can be text or numbers.

, on the other hand, is a function that will add the total number of numerical entries within a range if certain criteria are met. This means that sumif only works with numbers when adding up figures, so if your range includes any text, those will be ignored. Let’s walk through a simple example.

Suppose you have the following simple table of sales by type and # for each year from 2003-2009:


And what you’d like to know is how many sales of each type (in your store vs. by phone vs. online) were done in total. Countif will help you figure that out quickly. First, let’s enter the type of sale we’re interested in in a column to the right:

Next, we’ll type in the “countif” function. As you can see, countif takes two inputs, the range you’re interested in and the criteria that need to be met:

In our case, the data (store type) is in column B, so we input that into the first part of the countif function:

Next, we want each entry to be counted only if the type of sale was “store”, or what we’ve typed into cell E2:

Note that if we hadn’t typed in “store” in E2, we could type it directly into the function like this:

=countif(B2:B20, “store”)

By the way, remember to always put quotation marks (“”) around the criteria when working with text!

Either way, once we close the parenthesis and hit , we immediately get the result. There were 7 sales from 2003-2009 that took place at the store (one for each year).

Repeating the same thing for “telephone”:

We find the same result — 7 sales. Now let’s check “online”:

Turns out we had less online sales (maybe because we didn’t launch a website that accepted sales until 2005):

That’s fine and dandy, but not particularly useful info. How about if we wanted to know the total number of sales by type for the entire period? In that case, we’d want to use the sumif function.

For “store” sales, we’d enter in sumif and the appropriate inputs as
follows. First, sumif takes in slightly different inputs than countif.

The first input is the range that you’re interested in, very similar to countif:

The second input is the criteria, which in this case is also identical to what we put in for countif above:

But sumif also takes one additional argument: the range to be summed. In this case, we want to know the total number of sales, which is in column C:

Closing out the parenthesis, we find that we had 211 total sales in the store from 2003-2009:

Again, let’s repeat the same thing to find the total number of telephone sales:

…and online sales:

See the difference between what countif gives and what sumif gives?

Note that sumif can also take in different criteria. Let’s suppose we wanted to know the total number of sales that have taken place starting in 2008, regardless of type. To do this, we could make our range the year, found in column A:

and then add in the criteria of “>= 2008”:

Finally, we again want to sum up the total number of sales, which is in column C:

Closing out our final parenthesis, we get that we have had a total of 141 sales from 2008-2009:

Finally, just a quick example here will show what happens if you put in non-numerical data to sum using the sumif function. Let’s change the number of store sales in 2008 from “37” to “nonsense” and see what happens:

Notice how the calculations automatically ignore that entry, and the total number of store sales and the total number of sales since 2008 both drop by 37.

That’s it for now. A quick intro tutorial to using countif and sumif. Like most other Excel functions, these can get as complicated as you’d like (at the expense of readability), so always try to keep it as simple as possible.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

15 Feedbacks on "How to use the COUNTIF and SUMIF functions in Excel — a quick introductory tutorial"

mtLucy

Professional graduate thesis about this good post performed by thesis writing service or dissertation writing service can be the first point to the academic success.



Lisa Gallus

I happened to watch alot of the tutoral on the youtube video after reading the additional information and I have to say I got alot more out of the additional info vs the youtube videos. Although I watched with no sound, even the visuals at time were confusing;however I maybe would add that it was smaller than I would have liked the sceen to be even with enlarging. I’d like to go to the sites on youtube at my own time and rewatch the videos and give them the benefit of doubt as to the instructional materials.
And I have been trying to play around with the excel alot more now than I have in the past.



Justine Van Wyk

I like this tut. I would like to know the diff now between vlookup and sumif? Any comments?



Lillie

Thanks for the quick tutorial on Excel. I needed that since recently starting to use Word 2007. I am defintely going to review the remaining information so that I can become a quick expert. Agan, thanks for designing it in a layman’s format.



me

This worked fine, but I have a more complicated problem. How do you nest the countif when you have three criteria to meet. Example: you want to count all barrels in section a, but only if they are slated to be shipped to CA and if the barrels are less than 10 gal. I can get two criteria to work, but after that, I get “true”. Any suggestions?



Leslie

@ME – This can be fixed using an array formula, but it’s difficult to explain how to do this step-by-step without seeing the actual data. Feel free to send it to me if you’d like help.



Yespal

I want to count the mark more than 80 in different range of cells suppose[A5:A25]+[C5:C25]+[E5:E25]
How can I joint the different cells in one formula.

=countif(A5:A25 *then how to combine next c5toc25 and e5toe25*,’>80″&G4)
Where cell G4 contains the mark.

Can you suggest me please.



mAQSOODALI

i WANT TO COUNT WITH THREE CONDITIONS IF A



of candid photographers in chennai|candid photographers in chennai|pre wedding shoot locations in chennai|pre wedding shoot chennai|pre wedding shoot ideas chennai|candid wedding photography cost in chennai|candid photography for wedding in chennai|candid

I simply could not go away your web site before suggesting that I actually loved the usual info an individual provide for your visitors? Is gonna be again incessantly to inspect new posts



Jay

This is a HUGE timesaver and something I’ve been periodically trying to find for over 2 years. Thanks!!



Bruno Machavane

Espetacular…
Resolvi os meus problemas gracas a vossa ajuda.



Bruno Machavane

Espetacular…



Munir badar

Dear your site is too much beneficial for the people who want to learn excel.

i am facing a lot problem to manage date in count if and sum if formula,

like =&TODAY()

> AND < THAN DATE SETTING
TODAY()-7
THESE TYPE OF FORMULA ARE REALLY CONFUSING ME.

KINDLY HELP ME TO SOLVE THIS ISSUE.



Malcolm

I am having problems with the sumif and countif functions. I have a list of customers, products #,vendor, ext sales price, and year. looking for the correct function and will count the # of items purchased one time, the ext price, and year purchased for each vendor.

Please help. Thanks!



basit ali

very helpfull



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.