Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]
Posted on November 12th, 2008 in
Learn Excel  64 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.
 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("masterdatarange","city name")
More on this method of using countif and 4 other ways of using excel if () formulas
 Counting Valid Phone Numbers in a Range: Using operators < and > in countif() you can findout valid phone numbers in range like this:
countif("datarange",">"&1000000000)
 Finding number of customers in a city based on their phone number: This trick may not work perfectly. We can use
countif("datarange","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.
 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.
 Counting positive numbers in a range: Again we use the > operator to count the positive numbers in a range like this:
countif("datarange",">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("datarange")/countif("datarange","<>0")
 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("cellyouwanttolook","*hilton*")
will return 1 or 0.
 For sorting text: Read more on this at sorting text using excel formulas
 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.
Share this tip with your friends


Written by Chandoo
Tags: countif(), howto, Learn Excel, microsoft, Microsoft Excel Formulas, MS, spreadcheats, spreadsheets, sumif(), text processing, tips, tricks
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question



Leave a Reply
64 Responses to “Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]”
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!
@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.
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
[...] 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 [...]
Thanks for your tutorial,..
This is the first place i search when i have some difficulty in excel.
Keep writing …
@Myea: Thank you so much. I am happy you like my site and use it very much
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.
@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
Hi Chandoo,
Thank you very much. It worked.
I am trying to do a survey of data with count if. i can’t seem to do it, i need help. thanks
@Joanne’ did you check out our how to do employee satisfaction surveys in excel post?
chandoo.org/wp/2009/02/04/satisfactionsurveysexcel/
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 20070112 FE Twinkle  $412.99 Sleep
316 20070125 fi Twinkle Evaluation of Text Inserter Setup with GSITE software. V1_22 and RJ45 adapters. $446.96 Sneesy
317 20070125 fo Little Evaluation of Text Inserter Setup with GSITE software. V1_22 and RJ45 adapters. $446.96 Bashfull
345 20070205 fum Star  $853.96 Mickey
362 20070215 I How  $426.98 Minny
408 20070215 Smell I  $853.96 Pluto
412 20070221 The Wonder  $426.98 Donald
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?
@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.
@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().
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!
@Kyle.. You are welcome.. I am happy you enjoy the site.
[...] 8 ways to use SUMIF() and COUNTIF() formulas [...]
[...] Learn cool uses of countif and sumif [...]
[...] excel formulas: IF and Then, Vlookup, Offset, Sumif, Countif, Working with date and [...]
[...] 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 [...]
[...] Using COUNTIF and SUMIF formulas [...]
[...] 19: Introduction to COUNTIF and SUMIF Formulas [...]
[...] 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.“. [...]
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
@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.
[...] we know how to find sum of values that meet a criteria – we use either SUMIF, SUMIFS or SUMPRODUCT [...]
[...] Also, you should know how to use COUNTIF Excel Formula, it is so awesome, I wonder why MS hasnt called it MAGIC() [...]
[...] 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 [...]
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.
[...] COUNTIF & SUMIF Formulas [...]
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
@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/customcellformattinginexcelfewtipstricks/
or
http://chandoo.org/wp/tag/numberformatting/
.
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/wanttobeanexcelconditionalformattingrockstarreadthis/
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 march01. 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
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:
MARList
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
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
@Nico
Thats interesting and I can’t explain why Marc1 seems to be equal to Mar01 ?
However if you use =SUMPRODUCT((A2:A5=B2)*1)
It works correctly
@Hui, thanks for this solution!
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.
@Krishna
Can you please post or email me a sample of the data you have with some instructions
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.
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
@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
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)
I Must be having a bad morning to miss that
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.
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.
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.
@Giedrius
Have a look at Sumif or Sumifs
Refer: http://chandoo.org/wp/2010/04/20/introductiontoexcelsumifsformula/
Thank You, looked ,searched and here is what I have done so far,
=SUMIFS(D4:D9;C4:C9;”*likutis*”), only works with two columns, the thing is that, I want to add F4;F9:E4;E9 and 29 more. any help on this ?
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 410,000 gallons and then 1120,000 gallons and then over 21,000 gallons.
Any ideas….I am just a basic excel user…haven’t used all the functions before.
Lwatson814
Generically this style of problem is solved by something like:
=IF(B2< =3000,Fixed 1 + B2* Rate 1,IF(B2<10000,Fixed 2 + (B23000)* Rate 2,IF(B2<20000,Fixed 3 + (B210000)* Rate 3,Fixed 4+ (B220000) * 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
Thanks…I will try that and see how that works.
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……
[...] wildcards in COUNTIF formula page to get some [...]
i have duty schedule i want to asign like d=12hr, d*=24hrs
is it possible to make it onexcell sheet.
shahbaz
Thanks chandoo. I practiced & worked well…..
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
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
@Arul
What data defines a Boy or Girl ?
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/…???
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.
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.
=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?