May 3 2007

Sumif and Countif Formulas in Excel

There are many times when analyzing large amounts of data when you might want to sum or count a column or range based on certain criteria. One example is if you have a database of products you have sold, you might want to know the sum of profits for a particular product – which is where the sumif formula would come in handy. Or possibly for the same example you want to know how many instances of a particular item you have sold, which is countif’s formula specialty.

Let’s start by taking a look at the formulas:

COUNTIF(range,criteria)
SUMIF(range,criteria,sum_range)

Both the Countif and Sumif formulas have very similar criteria. When you are using the Countif formula you need to the it the range of cells that you want evaluated with a certain criteria. Sumif is much the same way with one exception, the sum_range can be different from the range you are evaluating with a specific criteria. We well show numerous examples below to illustrate how the formulas work and their differences.

Examples

Example 1: =COUNTIF(C2:C8,C2) will return 3. In this example, we are telling excel to count the number of times orders were placed for “Red” products – 3 instances were found in lines 2, 7 and 8.

Example 2 : =COUNTIF(D2:D8,”>10″) will return 3. In this example, we are telling excel to count the number of times orders were placed for that were greater than $10 (notice the item criteria needed to be placed in quotes this time since it was not a cell referent) – 3 instances were found in lines 4, 7 and 8.

Example 3 : =SUMIF(A2:A8,111,D2:D8) will return $14. In this example, we are telling excel to sum the prices (column D) of the any product in column A that equal “111″ – 2 instances were found in rows 2 and 5 which summed values $10 and $4.

Future Considerations

One of the next sections we will be adding to ExcelHints.com is there may come a time when you want to sum a range based on more than one criteria. In these cases you want to use the Ctrl-Shift-Enter (or CSE) formulas which use arrays to calculate. Because these get more involved than the straight forward Countif and Sumif formulas, we will save them for their own topic.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

18 Comments on this post

Trackbacks

  1. COUNTA and COUNTBLANK Formulas in Excel wrote:

    [...] ways built in already to count that range for you. One way we already explored is through the CountIf Formula, and also the Count Formula. Excel also features the CountA and CountBlank Formulas to make your [...]

    July 23rd, 2007 at 6:46 am
  2. Quick Way to Check for Duplicates in a Column | Excel Hints Blog wrote:

    [...] have already shown you how to use the formula in the SumIf and CountIf Foumulas post. But here I just wanted to mention this is an example where you can use [...]

    August 28th, 2008 at 11:27 am
  1. Mils said:

    I work with the sales team and they use a pieplien tracker to track all sales. The biggest problems is they’re not Excel prficient and when l get the spreadsheets back the fomatting and formulas are a mess.

    Question – Is there any way that l can fix the formatting in the spreadsheet without anyone changing it e.g. l want a black grid with headings with a blue baclground but if someone wanted to dlete of change it they can’t. I don’t want to password protect the cells as they need to enter data into them.

    Thanks for your help!

    October 11th, 2008 at 6:03 am
  2. john said:

    Hi Mils – Great question.

    It might be slightly different depending on what version of Excel you are using. Give this a shot though.

    Select all the cells you want to be able to edit the numbers for, select Format Cells and find the tab or section where you can uncheck “Locked” for the cell protection. Make all the formatting changes you want to make and then protect the sheet. By default, Excel will only allow users to edit the cell values and not the formatting.

    In Excel 2007, I know there are options where you can allow users to also edit the formatting, but that is not the default setting.

    Hopefully that answers your question and thanks for stopping by Excel Hints!

    John

    October 11th, 2008 at 10:19 am
  3. john said:

    I’m actually going to probably right a post about this soon, because it was a good question that many others might find useful.

    October 11th, 2008 at 10:20 am
  4. Mils said:

    Hi,

    Re:Vlookups/ Hlookups. I have a calendarized spreadsheet that goes from Jan to Feb and in each cell l’m pulling data through from a data sheet with H/Vlookups for each respective month.

    Issue: The problem is when l’ve got the formula working fine in January and l want to copy and paste it through to December, how can l do this without manually going into each month and changing the column reference i.e. let say January vlookup is looking at column 2 in the master data sheet then Feb should pull through coulmn 3 and March column 4 and so on. Usually what happens is that all months will reference column 2 only.

    Thanks for your help.

    October 13th, 2008 at 12:34 pm
  5. P K Gambhir said:

    Very useful for excel user community. A good job. Keep it up.

    November 18th, 2008 at 2:26 am
  6. john said:

    Hi PK – thanks for the encouragement. I’m glad you are finding the site useful.

    November 18th, 2008 at 11:41 am
  7. Macie said:

    I would like to know the formula for a line count. I produce a large spreadsheet and put in all my formulas, then break it down to individual tabs. Maintaining the master tab as ALL information and subsequent tabs broken down to salesman specific. Without having to look at the sheet. figure out the number of header lines, subtract those and the total line… I would like to just have a field populate the number of lines including on the sheet. Something like a summatin but not because I don’t want to add anything, just get a line count.

    Thank you!
    Macie

    October 23rd, 2009 at 2:46 pm
  8. john said:

    If the header line count and subtotals are all the same #, then couldn’t you just do a count or counta with those other lines subtracted? If you’re maybe specific, maybe we can come up with a solution.

    October 24th, 2009 at 12:19 am
  9. Rayann said:

    Having a real hard time, and it’s a formula i have used before, but cannot for the life of me remember how to do this. Lets say i have a total amount in cell A3 that if cell B2 has a 1, i want to multiply cell A3 by 1.1 to give a total in cell B3. If there is a 2 in cell B2, i want it to multiply cell A3 by 2.3 to give me a new total in cell B3. How in the world do i do this?

    February 9th, 2010 at 11:49 pm
  10. john said:

    Is that the whole formula that you would need or does it get progressively larger… for example if there is a 3 then it needs multiplied by 3.4?

    February 10th, 2010 at 12:12 am
  11. CHuck Evans said:

    I have a Q I can’t figure out. In my Database divided into weekly worksheets, I have 2 cols of interest by day: the date and the P&L. Then in my daily summary have a number of transactions each day
    (for example: countif(A13:A45, “mar 8″) = 7
    If I have transactions over a count of 4, I want a sum of those >4 transactions. So here I want to sum trans 5,6,7.
    I have been playing w/ SUMIF but can’t get it to work. I use the datecol for the the range, the countif function for the criteria and the P&L in the sum_range, but it doens’t like it

    Thanks
    Chuck

    March 13th, 2010 at 8:14 pm
  12. jotdeep said:

    Hi,
    I have a question i used excel 2007 i make a spread sheet on excel i would like to know is there any formula by which i make a link suppose if i change a one value then the other values automatically changed on that row i got a data eg: shows below
    From To SUM %
    0.550 0.850 221 5.84
    0.900 1.200 565 14.94
    1.250 1.550 1060 28.02
    1.600 1.900 1086 28.71
    1.950 2.200 503 13.30
    2.250 2.500 348 9.20
    1.300 2.500 2891 76.42
    1.750 2.500 1450 38.33

    September 4th, 2012 at 5:27 pm
  13. Andy said:

    Hi John. It’s just a shame that you weren’t a teacher at my school 35 years ago. I googled my query on Excel formulas and yours is the only tutorial that seems to be in my language! (Plain English). Sorted my problem out in minutes.
    Thanks very much……….Andy.

    September 15th, 2012 at 12:23 pm
  14. john said:

    Thanks so much Andy. I’m glad they were useful.

    September 21st, 2012 at 9:29 pm
  15. Joseph said:

    Hey !! Great Video, Really helpfull.. but I have some qtuieson for the SUMIF function .The definition of SUMIF is add some numbers data in one coloumn, with certain condition in the other coloumn. And my qtuieson is .. what if i want to add all numbers data in one coloumn, but except the data that have certain condition in other coloumn..??thank you , I hope you can help me with that.

    December 12th, 2015 at 6:32 pm
  16. Joseph said:

    Hey !! Great Video, Really helpfull.. but I have some qtuieson for the SUMIF function .The definition of SUMIF is add some numbers data in one coloumn, with certain condition in the other coloumn. And my qtuieson is .. what if i want to add all numbers data in one coloumn, but except the data that have certain condition in other coloumn..??thank you , I hope you can help me with that.

    December 12th, 2015 at 6:33 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives