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("master-data-range","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("data-range",">"&1000000000) - 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. - 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("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") - 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. - 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.
85 Responses
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
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/satisfaction-surveys-excel/
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
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.
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.
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.
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/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/
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
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
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/introduction-to-excel-sumifs-formula/
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 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.
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
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……
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?
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
@Ana
Not really sure what your after but the basic formula is:
=COUNTIF(B2:B25,”EZ”)
In this case it returns 17
Yes, but I want the formula to return the value that when they are counted is greater than 10 but less than 25. In this case it would be 7. Do you know how I can write that formula??
Thanks!
What if it is less than 10 or greater than 25?
Well…..
Ok, since you asked! 🙂 I would like 3 separate formulas: One that returns the count if the value is 10 or less, then the next would be the count if it is greater than 10 but less than 26 and then the final wouns be greater than 26 but less than 51. This is going to be used to calculate commissions at 3 different levels.
I appreciate any help or guidance you could provide!
Ana
@Ana
You will want something like:
=IF(COUNTIF(B2:B25,”EZ”)<10, 2,IF(COUNTIF(B2:B25,"EZ")<27,1,0))
Where it will return 2 if the Count is <10
It will return 1 if the coun is less than 27 and 0 (when the count >=27) other wise
So in your case you want the count minus 10 if it is between 10 and 27
=IF(COUNTIF(B2:B25,”EZ”)<10, 2,IF(COUNTIF(B2:B25,"EZ")<27,COUNTIF(B2:B25,"EZ")-10,0))
Note I have replaced 1 with COUNTIF(B2:B25,"EZ")-10
Hui,
You’re the bomb! I have everything I need now. Thanks for all your patience and help.
HUI,
Sorry….I should have posted my formulas for anyone else that was following. Here are the 3 formulas I used:
=IF(COUNTIF(E2:E92,”EZ”)>0,IF(COUNTIF(E2:E92,”EZ”)10,IF(COUNTIF(E2:E92,”EZ”)25,IF(COUNTIF(E2:E92,”EZ”)<51,COUNTIF(E2:E92,"EZ")-25,50))
They all work!
Thanks again.
i want to ALL Short key Advanced Excel.
Using formula calculate How Many Names are in below mentioned Range
10
Sumit
20
Ram
30
Rohan
40
Mohan
50
Reeta
60
Swati
70
Poonam
80
Mukesh
90
Shailender
100
@Srishti
Assuming the range is A1:A20
You can use:
=COUNTA(A1:A20)-COUNT(A1:A20)
Or a simpler formula is:
=COUNTIF(A1:A20,”?*”)
In a class, the name start with Maung, Saw, Min & Khun are Male and Ma, Nan,Mi are Female. How to separate Male or Female using excel 2007. Please give me answer for my Question. Thank you .
HI…..
chandoo, org im really thak to you for sending new new formulas with examples i hope in furture too….
really we can learn more and more from here..
hey we have 4 to 20 no for experience such that if one man complete 6 months he can get 4 marks in other way from 6 months to 17 months he get 4 marks and after that from 17 to 27 months 8 marks and onwards plz sujjest if formula for such case
Dear chandoo,
I want to count the cell which are green colored in between the particular range of cells. for ex: (A1:D20) In which may cells are blank and some cells are green and we can not use filter to count.
How can i able to count those cells by Using formula is there any way to count it??
Having a problem! I need to find the threshold (999999.99) of revenue and add 14% vat once this has been reached. I have this at the moment =(C9>=999999.99,0.14*C9,0.14*C9) and this returns the value for all amounts. I only need it for threshold.
Hi,
I need to get the shift timing for the below dates as:
if the time range is from 06:00 – 15:00 then it should be “1st shift”
if the time range is from 13:30 – 22:30 then it is “2nd shift” etc
Date
4/2/2017 21:39
3/2/2017 19:23
2/10/2017 22:45
3/7/2017 6:27
1/24/2017 11:37
4/3/2017 10:31
4/1/2017 12:08
3/10/2017 10:06
3/6/2017 6:40
3/3/2017 10:27
2/28/2017 21:28
2/15/2017 7:20
2/6/2017 4:43
4/30/2017 22:52
4/30/2017 22:05
4/30/2017 20:22
4/28/2017 10:58
4/25/2017 23:09
4/25/2017 10:24
Tried using IF but no lock.
Any help would be of great help. Thanks in advance!
assume 4/2/2017 21:39 is in A2:
B2: =if(mod(B2,1)<(15/24),"1st Shift","2nd Shift")
i have a little problem which i want solution.
i have a table like this:
a,b,c,d,j on separate column
e,a,d,f,m
g,h,c,a,s
b,d,g,e,d
how will i look for the pairs in this table
because the pairs here are
a,d
c,a
d,e
and which formula will i use to find the pairs. please help me excel Gurus.
and another one is
if they are figure like
10,20,21,26,80
10,45,42,44,80
15,21,23,26,32
45,42,74,76,50
26,10,45,88,89
which formula can i use to bring out all the pairs in the list of items
using cell A1:E5 as the cell range
Hi Chandoo.
I wanna ask how to count the numbers of training in each affiliate and warehouse. and the formula will be in other sheet of excel.
please see my below table
Warehouse Training Name Training Type
Safco SHEM 10 Affiliate
Hadeed SHEM 12.06 Affiliate
Ibb SHEM 01 Warehouse
Albayroni SHEM 11 Warehouse
@Mohammed… Interesting question. But I am not sure I understand all of it. Can you tell me what should be the sample answers?