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

Posted on November 12th, 2008 in Learn Excel - 22 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.

| More
Subscribe for PHD Email updates and get a free excel e-book with 95 tips & tricks

Comments
DBrueggeman November 13, 2008

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!

Chandoo November 13, 2008

@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.

Myea Amelia December 17, 2008

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

Chandoo December 19, 2008

@Myea: Thank you so much. I am happy you like my site and use it very much :)

Raj Kiran March 5, 2009

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.

Chandoo March 5, 2009

@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

Raj Kiran March 6, 2009

Hi Chandoo,

Thank you very much. It worked.

joanne` March 14, 2009

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

Chandoo March 15, 2009

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

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

Forrest Erickson May 11, 2009

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

Tania May 11, 2009

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 May 11, 2009

@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.

Chandoo May 11, 2009

@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().

Kyle June 3, 2009

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!

Chandoo June 3, 2009

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

RSS feed for comments on this post. TrackBack URI

Leave a comment

   Name (required)

   E-mail (required, never displayed)

   URL


If you have a question, please ask in the forums

Recommended Excel, Charting, VBA books