Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]

Posted on November 12th, 2008 in Learn Excel - 66 comments

If for every countif() I write excel paid me a dollar, I would be a millionaire by now. It is such a versatile and fun formula to work with that I have decided to write about it as third post in our spreadcheats series.

  1. Using COUNTIF() to replace pivot tables: We all know that you can use countif() to replace pivot tables for simple data summarization. For eg. if you have customer data in a table and you would like to know how many customers you have in each city you can use countif() to find that.
    countif("master-data-range","city name")
    More on this method of using countif and 4 other ways of using excel if () formulas
  2. Counting Valid Phone Numbers in a Range: Using operators < and > in countif() you can findout valid phone numbers in range like this: countif("data-range",">"&1000000000)
  3. Finding number of customers in a city based on their phone number: This trick may not work perfectly. We can use countif("data-range","22*") to findout total customers in Mumbai (since all Mumbai phone numbers begin with 22)
    Note: This method works as long as phone numbers have identifiable calling codes and stored as text. To covert a number to text you can use text() or append an empty space to the number.
  4. Pattern matching: Often when you extract data from other sources and paste it in excel it is difficult to process it when the formats are not consistent. For eg. when you copy address data of a bunch of customers and need to know how many customers are in “New York” you can use countif like this: countif("data range", "*new york*"), the operator * tells excel to match any cell with new york in it, not necessarily at the beginning or end of the cell.
  5. Counting positive numbers in a range: Again we use the > operator to count the positive numbers in a range like this: countif("data-range",">0").
    A very good use of this trick is when you need to calculate average of a bunch of numbers but need to exclude zeros: sum("data-range")/countif("data-range","<>0")
  6. As a replacement to FIND(): Excel FIND() is powerful formula to find if a particular text occurred in another text. But one problem with find is it returns #value! error if the value you are looking for is not in the input cell. What if all you need to know was whether your cells had a particular value or not?You are right, you can use COUNTIF() for that too, like: countif("cell-you-want-to-look","*hilton*") will return 1 or 0.
  7. For sorting text: Read more on this at sorting text using excel formulas
  8. Findout the number of errors in a sheet: The beauty of countif() is that you can even count error cells. For eg. you can use it like: =COUNTIF(1:33,"#VALUE!") to findout how many #VALUE! errors were there in the rows from 1 to 33. This can be useful if you are building a complex model and need to keep track of errors.

Most of the tricks should work with SUMIF() as well.

If you like this, read the other posts in the spreadcheats series. It is a 30 post series (3 posted so far) that aspires to make YOU very good in using excel to solve day to day problems.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

66 Responses to “Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]”

  1. DBrueggeman says:

    Chandoo –
    I’m on of those LifeHackers that have invaded your blog. I must say that I love the simple style you use – I’ve already pointed a number of coworkers to your blog for hints and tricks to relieve myself of the some of the work that comes with being the resident Office guru.
    One thing that would be great for this series is a review of v/hlookup vs. the Index/Match combination. I can always tell when someone ready to go to the next level when they discover vlookup but using that command really pumps up the file size in older versions of excel (pre ’07). Index(entire range, use Match on the vertical list, use Match on the horizontal list) is so much more versatile – you can change the order of columns, rows, and pick out items that are left of the lookup. It also is very easy on the file size (a plus when you have to email files under the draconian limits of the mail attachment size.
    Look forward to your future posts – you’re my newest Google Reader feed!

  2. Chandoo says:

    @DBrueggeman: Welcome and thanks alot for the sweet words :)

    I am soooo happy you were able to use some of this and sharing it with others.

    I am going to write about offset / vlookup as part of the spreadcheats, may be this week. I hope some of these questions can be answered.

    • Chahine says:

      first when i read your website name i found it funny i thought what that chandoo can do with that similar name but when i continue reading your work and find out how its pro and well explained im surprised man and be sure you are the first and only one in my bookmarks concerning Excel !!
      you are one from few peoples that are giving and helping without asking anything in return
      all the best for you and for Chandoo.org
      Regards,
      Chahine

  3. [...] name and the list is sorted on column B, we can use offset() and match() combination along with countif (ahem!) to determine which range to use for project cell [...]

  4. Myea Amelia says:

    Thanks for your tutorial,..
    This is the first place i search when i have some difficulty in excel.
    Keep writing …

  5. Raj Kiran says:

    Hi Chandoo,
    I am trying to provide summary analysis from a Raw data for eg production tickets. Here I would like to display a table which shows no. of High, Medium and Low issues with respect to OPEN, CLOSED and HOLD parameters. I wanted to do without using excel pivot tables. Can you please let me know the way on doing this.

  6. Chandoo says:

    @Raj: Assuming you have a production ticket table like this:

    A – B – C
    1 Ticket – Priority – Status
    2 t1 – high – open
    3 t2 – low – hold

    etc.

    You can add one more column in the end with the formula: =b2&c2

    Then, create another table like this:

    E F G H
    1 [ ]High Medium Low
    2 Open
    3 Closed
    4 Hold

    then in cell corresponding to high, open you can write a formula like,
    =countif(d2:d100,f$1&$e2)

    copy paste the formula over the 8 remaining cells and you should have what you are looking for.

    let me know if you are facing some difficulty

  7. Raj Kiran says:

    Hi Chandoo,

    Thank you very much. It worked.

  8. joanne` says:

    I am trying to do a survey of data with count if. i can’t seem to do it, i need help. thanks

  9. Chandoo says:

    @Joanne’ did you check out our how to do employee satisfaction surveys in excel post?

    chandoo.org/wp/2009/02/04/satisfaction-surveys-excel/

  10. Dear Chandoo,
    Given a table with fields for invoice dates and invoice total, I want to take a years worth of data and break it into monthly sales to make bar graphs.
    I am trying and failing to get sumif to help with out luck
    I read with interest your tutorial on countif but it is still beyond me.
    Any idea?

    Forrest

    Data:
    No Date Contact Name Subject Description Total Account Name
    382 2007-01-12 FE Twinkle - $412.99 Sleep
    316 2007-01-25 fi Twinkle Evaluation of Text Inserter Setup with GSITE software. V1_22 and RJ45 adapters. $446.96 Sneesy
    317 2007-01-25 fo Little Evaluation of Text Inserter Setup with GSITE software. V1_22 and RJ45 adapters. $446.96 Bashfull
    345 2007-02-05 fum Star - $853.96 Mickey
    362 2007-02-15 I How - $426.98 Minny
    408 2007-02-15 Smell I - $853.96 Pluto
    412 2007-02-21 The Wonder - $426.98 Donald

  11. Tania says:

    Hi, I’m having problems with SUMIF when taking values from other excel files: if the files are not open it gives me error “#value!”. Since I’m using about 50 different files I can’t open and close all of them all the time (even doing it with woorkbooks_open/close).
    Any suggestion?

    • Chandoo says:

      @Tania: Working with several files is a very expensive operation. But it is still possible. By using the full physical path of the file in the reference of SUMIF you can get the contents of the files even when they are closed. Let me know if you face some difficulty implementing this.

  12. Chandoo says:

    @Forrest: You can use the month() function to extract month from any date. So create a new column where you can extract this month data and then pass it to sumif().

  13. Kyle says:

    I discovered your site about 5 months ago and use is everytime i hit a hurdle in Excel or need creative graphing ideas. Because of your site my bosses consider me our teams Excel expert. I think what makes this site the best resource for intermediate Excel users like me is that you explain everything simply and in a stepwise fashion and then you make available the ground covered in an Excel file which i can save and against which i can compare my own interpretation of the tip using my own data. I guess that’s just a long way of saying thanks a mil, man!

  14. Chandoo says:

    @Kyle.. You are welcome.. I am happy you enjoy the site.

  15. [...] the sales person named “Luke” sold. This is simple, you will write a SUMIF formula [examples] and use the Name column as “criteria range” and Sales column as “sum [...]

  16. [...] During a recent consulting work with a client I had a requirement to sum up values that meet some criteria across columns and I wasn’t sure if the SUMIF would hold. Boy, I was wrong. It worked nicely and I still stand by my statement “If I get a dollar for every COUNTIF / SUMIF I write, I will have million dollar bills.“. [...]

  17. izabel from brazil says:

    I love this site and recomend to all my friends.
    Now I need an help me, please
    I’m trying to use the CountIf() with “>= and =1 and <=3"))

    month $
    9 $ 1,00
    1 $ 2,00
    2 $ 1,00
    8 $ 3,00
    3 $ 5,00

    tks again Izabel

  18. Chandoo says:

    @Izabel… Thanks for your comment and love. Welcome to PHD.

    You can write SUMPRODUCT formula like this:

    =SUMPRODUCT(- -(ROUNDUP(A1:A10/3,0)=1)) to find the count of all the values =1

    replace the =1 with =2 to get =4.

  19. [...] we know how to find sum of values that meet a criteria – we use either SUMIF, SUMIFS or SUMPRODUCT [...]

  20. [...] Also, you should know how to use COUNTIF Excel Formula, it is so awesome, I wonder why MS hasnt called it MAGIC() [...]

  21. [...] to use the intermediate level functions within Excel. These include: sum, sumif, macro’s, logic (if, then, else), auto sum, filtering, auto subtotal, sorting, charting, pivot [...]

  22. Thameem says:

    Chandoo, thanks for your posts.
    No Revenue Duration
    316575505 23.63 207.60
    216552606 78.07 689.80
    23880712633 36.73 316.50
    316364514 0.32 2.50
    216404819 45.87 632.57
    2016818256 51.16 705.95
    2416540148 57.73 899.65
    2316935400 45.60 383.75
    291880656194 6.45 80.53
    2416378059 8.23 64.95
    2336497000 10.26 89.20
    2346126773 13.58 193.97
    2356261454 0.41 4.22
    231980547017 7.00 60.15
    231995883 107.48 746.78
    316105223 6.90 89.57
    4316739635 6.80 96.17

    I want do calucate the band wise like the below. I want count how many numbers are in the below band and their sums as well. Kindly advice me, which formula to use.

    Score Bands
    0
    1
    2
    5
    10
    15
    20
    25
    50
    100
    >=100

    Thanks in advance.

  23. Edward Gitau says:

    hi,
    I am really having a problem with Data comparison and your blog has been like an eye oppener to me so thank you.
    I have like a “special request”, do you mind telling me how i would go round the issue i am currently facing? I have two systems at the office. when i copy Data from one place and paste it to another the Data formats are slightly different. one is 07:00:00 AM and the other is 07:00:00 with no AM nor PM you basically have to do that on your own.
    I do nto have any strong knowlage with excell so please bare with me. so the issue is i need a formula that will look at Data in cell A and B then if there is a match it higlites them. Or if it were possible whenver it could get a match, it pastes the names(that match) from cell C and pastes them to D.
    your soonest assistance will be appreciated. My job almost depends on this.
    Thanks in Advance

  24. Hui... says:

    @Edward
    Times are stored as numbers between 0 Midnight and 1 Midnight with 0.5 as Mid Day
    .
    Display Format
    The way you see them is controlled by the display Format
    .
    Select the cells
    Ctrl 1 or Right Click and Format Cells
    On the Number Tab
    Select the display Format you want
    Close
    .
    Have a read of
    http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/
    or
    http://chandoo.org/wp/tag/number-formatting/
    .
    You can also use the Format Painter to copy cell formatting from one area to the other
    Its the small paint brush on the Home, Clipboard tab
    .
    Conditional Formatting
    To highlight where cells match you want to use Conditional Formatting
    http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/

  25. Nico says:

    Hi Chandoo,

    Checked your site on a “countif” issue, but could not find a solution.
    My problem is dat in de data I have names (not months) names starting with MAR. eg MAR01 and MARC1.
    Applying a count if on this data adds up both names as they are both march-01. But in really MAR01 and MARC1 are both names for totally different groups.
    I already converted to text, added ‘ in front, but nothing worked… do you have a solution?

    Thanks in advance,
    Nico van Bergen

    • Nico says:

      I noticed some recent activity on the countif formula. Still the reactions do not solve my problem as described above. Let me show an example:

      MAR-List
      Criterium
      =COUNTIF(A2:A5,B2)

      MARC1
      MARC1
      4

      MARC1
       
       

      MAR01
       
       

      MAR01
       
       

      How can I prevent that Excel reeds dates in column A instead of names?

      Thx, Nico

      • Nico says:

        Second try for better lay out:

        List, Criterium, Formula (COUNTIF(A2:A5,B2))
        MARC1, MARC1, 4
        MARC1
        MAR01
        MAR01

        As the formula counts all  “MARC1″ I would expect the result being 2, not 4.

        Rgds, Nico

  26. Krishna says:

    Hi Chandoo,

    I am trying to count the number of previous cells that are greater than zero. The tricky part is it should stop counting if hits a previous cell with zero value. Which means at current cell it should count the values greater than zero upto a point where the previous cells have greater than zero values and stop counting when one of the previous cells have a zero value. 

    Thanks for your help. 

    • Hui... says:

      @Krishna

      Can you please post or email me a sample of the data you have with some instructions

       

      • Krishna says:

        Hi Hui,

        Thanks for your reply. Below is the sample data with manual calculation.

        The date and value column are the one user provides and the count column should be generated with a “COUNTIF” of “SUMIF” formula such that at each cell it calculates the count of values greater than zero for previous cells until it reaches one of the previous cell that has a zero value. Basically I would like to know how many values are greater than zero before.

        Date     Value   Count
        1/1/201210
        1/2/201201
        1/3/201200
        1/4/201200
        1/5/201200
        1/6/201210
        1/7/201211
        1/8/201212
        1/9/201203
        1/10/201210
        1/11/201201
        1/12/201200
        1/13/201200
        1/14/201200
        1/15/201200
        1/16/201200
        1/17/201210
        1/18/201211
        1/19/201212
        1/20/201213
        1/21/201214
        1/22/201201
        1/23/201200
        1/24/201200
        1/25/201200
        1/26/201200
        1/27/201200
        1/28/201210
        1/29/201211
        1/30/201212
         
        Thanks.

      • Krishna says:

        I see that in my previous post the columns are not seen with spaces. I have inserted commas to differentiate between each columns.

        Date      Value   Count
        1/1/2012,1,0
        1/2/2012,0,1
        1/3/2012,0,0
        1/4/2012,0,0
        1/5/2012,0,0
        1/6/2012,1,0
        1/7/2012,1,1
        1/8/2012,1,2
        1/9/2012,0,3
        1/10/2012,1,0
        1/11/2012,0,1
        1/12/2012,0,0
        1/13/2012,0,0
        1/14/2012,0,0
        1/15/2012,0,0
        1/16/2012,0,0
        1/17/2012,1,0
        1/18/2012,1,1
        1/19/2012,1,2
        1/20/2012,1,3
        1/21/2012,1,4
        1/22/2012,0,1
        1/23/2012,0,0
        1/24/2012,0,0
        1/25/2012,0,0
        1/26/2012,0,0
        1/27/2012,0,0
        1/28/2012,1,0
        1/29/2012,1,1
        1/30/2012,1,2

        • Hui says:

          @Krishna

          Interesting problem

          I understand your logic, I don’t have an immediate answer
          I will have to have a think about it.

          You may want to post the question on the Chandoo.org forums
          http://chandoo.org/forums/?new=1

        • Chandoo says:

          Interesting question as Hui says.

          I may have a solution.

          Assuming your data is in A1:B20 (A with dates & B with the numbers)

          in C2, write =IF(B1=1,C1+1,0)

          and drag it down.

          In C1, write =IF(B1=1,0,1)

          This is producing same results as yours except for cell C22 (in your data above, you say the value should be 1, where as formula shows it as 5)

          • Hui says:

            I Must be having a bad morning to miss that :(

          • Krishna says:

            Chandoo,

            Thank you very much…It works like a charm….You are awesome

            Thanks Hui…….I appreciate you both for taking time in answering my question…

            Thanks,
            Krishna.

  27. Jeff says:

    You have some useful info here.  I am tryng to use COUNTIF to count the number of < and = signs, the actual operators themselves.  The ~ that you can use to put before special characters, like * and ? does not work for < and =.

    Any help you can provide would be greatly appreciated.  

  28. Giedrius says:

    Hey, a lot of useful info here. Maybe do You know, how to sum numbers(money spent)  if there is a word “food”, but cell to sum with a number is from the right of the cell with a word.

  29. lwatson814 says:

    Having trouble with a formula that uses multiple if/then statements.  It is a multiple rate structure that is base on a given variable.  IE….if a customer uses 25,000 gallons of water he is charged based on the rate structure of usage between 0 and 3,000 gallons, and another rate for 4-10,000 gallons and then 11-20,000 gallons and then over 21,000 gallons.  
    Any ideas….I am just a basic excel user…haven’t used all the functions before.

    • Hui... says:

      Lwatson814

      Generically this style of problem is solved by something like:
      =IF(B2< =3000,Fixed 1 + B2* Rate 1,IF(B2<10000,Fixed 2 + (B2-3000)* Rate 2,IF(B2<20000,Fixed 3 + (B2-10000)* Rate 3,Fixed 4+ (B2-20000) * Rate 4)))

      Where you accumulate the rates up to your value as a fixed amount then multiply the difference by the rate above that amount

  30. Afsar Ali says:

    hey
    em afsar ali from pakistan.
    I found your tuturials very informative and helpfull.
    I want you to help me out regarding duplicate entry in excel.
    what I need is to prevent duplicate entry of clients when any two or three cells in the row matches with another cells in the other row by giving a warning messege at your ealier considrations. I need this despretly..
    waiting……

  31. shahbaz says:

    i have duty schedule i want to asign like d=12hr, d*=24hrs
     
    is it possible to make it onexcell sheet.
     
    shahbaz

  32. Selvam.V says:

    Thanks chandoo. I practiced & worked well…..

  33. arul says:

    pls clarify my doubt

    excel

    column (a1:a20) ten boys girls name
    column (b1:b20) some caste example : sc,bc,mbc,oc,st,ss

    my question

    how to count the no.of male in sc/bc/mbc/ etc……
    pls answer me

  34. arul says:

    pls clarify my doubt

    excel

    column (a1:a20) ten boys and ten girls name
    column (b1:b20) some caste example : sc,bc,mbc,oc,st,ss

    my question

    how to count the no.of male in sc/bc/mbc/ etc……
    and no.of female in sc/bc/mbc/ etc……
    pls answer me

  35. arul says:

    Sir

    Sn Name Sex Caste
    1 Arul Male MBC
    2 Jothi Female BC
    3 Kumar Male SC
    4 Anitha Female BC

    MY Question

    1. The Above Excel data typed the lot of students list iam asking for
    How to Count the No.of Male in SC/BC/MBC/…???

    How to Count the No.of Female in SC/BC/MBC/…???

  36. Mike says:

    Hello,
    I really like how you did the formula for tracking the repeating customers at a store for the last 30 days.
    =IF(C8>TODAY()-30,IF(SUMPRODUCT(–($B$4:$B$53=B8),–($C$4:$C$53>(TODAY()-30)))>1,”Repeated”,”Not Repeated”),”Not Repeated”)

    I am trying to track license plates for the last 30 days, and making a column that will automatically count the number of times a license plate shows up in the last 30 days. Can I get any help with that? Thank you very much for a great website.

  37. Mike says:

    Oh yeah, I would also like the cell to change color (yellow if 9, and red if 10 or above). I can do this part on my own now, but to add it to an existing formula, is what I am not sure about.

    Again, thank you for any help.

  38. VJ221 says:

    =SUMIF(‘[filename1.xlsx]Sheet1′!$A:$A,C12,’[filename1.xlsx]Sheet1′!$Y:$Y)+SUMIF(‘[filename2.xlsm]Data from ABC’!$A:$A,C12,’[filename2.xlsm]Data from ABC’!$Y:$Y)

    The above formula is being used in ‘filename3′, however, if filename1 and filename2 are closed it gives and error and I have to set the entire formula again. To avoid this how can we give the file name as a cell reference

    I am using the following formula, wherein I have given the entire file name in cells A10, A11, B10, B11, but it is not working.
    =SUMIF(A10,C12,A11+SUMIF(B10,C12,B11)

    Can you please suggest a crack to this?

  39. Ana Preston says:

    Help!

    I am trying to calculate by using countif for the number of “EZ”. I want the formula to return a number value if the count if is >10 <26. using the following:

    EZ
    EZ
    EZ
    12
    EZ
    6
    3
    EZ
    EZ
    EZ
    EZ
    EZ
    6
    EZ
    3
    3
    EZ
    EZ
    EZ
    EZ
    EZ
    EZ
    12
    EZ

Leave a Reply