Friday, October 23, 2009

Excel Formulas : Countif Multiple Criteria

Many times we need to perform a count based on multiple criteria.

This is very simple if you are using Excel 2007. You have standard excel function Countifs which lets you do the count based on multiple criteria.

But if you are using Excel 2003 this becomes tricky as you do not have any standard function that lets you do this. However you can do it using Pivot Tables. Sometimes you just need the count based on multiple criteria to be put into a report and using Pivot tables for such report may not be a viable option.

In such scenarios you need to go for a workaround using excel formula. Considering you have a data table like this and you want to know the number of customers in North Area serviced by Sales Man by name of Ram.

If you have Excel 2007 then it very simple just use
=COUNTIFS($B$13:$B$28,"North",$C$13:$C$28,"Ram") and it will give you count result 3.

For Excel 2003 users My favorite for such kind of calculation is Sumproduct function and you can do it with the help of following formula.
This formula will give you result as 3 customers.
You can add as many conditions here but be sure that the height of the range is same for all ranges mentioned in this formula

This actually is a workaround for another method array formula.
=SUM(($B$13:$B$28="North")*($C$13:$C$28="Ram")) confirmed with CTRL+Shift+Enter
Once you confirm this formula with CTRL+Shift+Enter it will add {} to the formula which will be visible in the formula bar only.

Look at the screen cast below to know the difference it make to the normal formula once confirmed as CSE formula. Look for the addtional {} added to formula

Download file having countif multiple conditions formula

You may find it difficult to enter array formula that is why I suggest you to go for SUMPRODUCT method.
To know more about array formulas you can read

Introducing array formulas in Excel



  1. I have a column of invoice numbers in an Excel 2003 file. In the next column, I want to do something like a VLOOKUP to see if there is a PDF file of the same name as the invoice number in a certain directory on the network. Can you do a comparison of a text from an Excel cell to names of PDF files.

  2. Hello John

    Welcome to my blog...

    Vlookup will be possible if you have file names in also in your excel file. So you will need to get the list of file names from your file directory on the network. Excel macro for getting file name is given below

    Sub ListFiles()
    F = Dir("C:\filepath\*.PDF")
    Do While Len(F) > 0
    ActiveCell.Formula = F
    ActiveCell.Offset(1, 0).Select
    F = Dir()
    End Sub

    You will need to change C:\filepath to your actual path on network. On running this macro you will get the list of PDF file names in excel.

    Now I hope doing a Vlookup will be easy.

    Let me know if you need to know more about this.


  3. I have a spreadsheet which is attempting to count multiple criteria on another tab within the same spreadsheet.

    If the name of the spreadsheet being referenced is "Test", how does that change the COUNTIF formula for 2003 and 2007?

    Thank you in advance.

  4. Hello Paymon

    Welcome to my blog. Considering the same data as in blog post above. But this time data in on sheet named "Test". The formula in this case will be

    This formula will work in Excel 2003 and 2007 both.

    However if you have Excel2007 than you can use following formula also


    Yogesh Gupta

  5. hi yogesh, plz help me in this..

    now i need count of rows containing raam i.e.6
    and count of rows containing shaam i.e.2

  6. @karandeep,

    You can do this with the COUNTIF function...


    Just change the range as needed (I used absolute references in case you planned to copy the formul). Oh, and notice the use of the asterisks... they are wildcards standing for zero or more characters at their location within the text. If you want to put the names in cell rather than hard coding them into the text, you would concatenate the cell with the asterisks (let's assume the name is in E1)...


  7. Mr. Gupta,
    I am bookmarking your site and will be back often.
    Chip Dempsey

  8. @Rick - Thanks for your response to Karandeep

    @Chip Dempsey - Welcome to my blog, you can also subscribe to email updates and feed for regular updates on new posts.

    Regards//Yogesh Gupta

  9. Thank you for having this page! I have been struggling with the task of counting instances of words with multiple criteria. This post really made it clear what I need to do! Now I'm done with my task right quick!

  10. Hi Yogesh, can u pls tell me on how to count multiple opbects in a cell?

  11. Hi Yogesh,
    Your blog is excellent and very helpful who are learning excel.
    Kindly help me with the formula for the following:
    I'm using the following formula to calculate time =SUMIF(H21:H266,AG2,I21:I266) where AG2 is a parameter. To count the no. of times a parameter occurs using =COUNTIF(H21:H266,AG2).

    But the problem is this calculation is based on single unit, individuals filling the sheet are required to select the parameter everytime if there is a repeatition.

    Is there any formula where we can specify numbers 1-50 (dropdown) for units and they can just select the parameter put across the no. of units for it.

    Kindly let me know your contact details for further discussion.

    Thanks & Regards,

    Gauri R.

  12. Hi Yogesh

    Your multiple formula SUBPRODUCT works fine with alpha and not for numerics. Please help me to do same formula for numerics.

    Many Thanks

  13. Hi Yogesh,


  14. Hi Shabry

    This formula works with all type of data. Let me know how are you using it. It will be better if you can send your file to me along with formula you have used. Pls send file to

    yogesh at yogeshguptaonline dot com

    Regards//Yogesh Gupta

    1. days name status
      1 tom done
      2 tom pending
      3 tom done
      4 john pending

      Hi yogesh, i need two formula.
      1.i need to count how many pending with john
      2.and how many pending with john for morethan 2 days.

      Could you please help me. Kindly send your answer to my mail id ""

  15. Hi Yogesh,

    I'll send the file by email.


  16. Hi Yogesh,

    I have created a spreadsheet with four Columns where i have Task that has a drop down(Training,Productivity,Break and so on.)Start Time, End time, Total time spent.

    Now in a cell i want to calculate total number of hrs spent on particular task.

    Could you suggest me on this..


  17. Hi Ganesh

    You can use sumif formula using task and total time spent column. Send me your file in case you need help with the formula.

    Regards//Yogesh Gupta

  18. How could i get this to work

    2 columns
    A B
    "First Name", "Last name"
    John Smith
    John Carry
    Bob Laurance
    John Apple

    Now I want to use countifs to get a count of all John's in column A but only if column B is Smith "or" Apple?


  19. @Yossi,

    Give this formula a try...


    Of course, you can put the names in cells and then use cell references instead of hard coded names in the formula if you want.

    Rick Rothstein (MVP - Excel)

  20. This comment has been removed by the author.

  21. Hi Yogesh,
    I'm having a little trouble with my formula, and I have tried to use your Sumproduct method. I have Excel 2003.

    Here it is:
    =SUMPRODUCT(('\\filename\file\file1\Inspections\Inspection LOG\[Master Inspection LOG.xls]NBT'!$B:$B="Site Visit")*('\\filename\file\file1\Inspections\Inspection LOG\[Master Inspection LOG.xls]NBT'!$D:$D,"<="&DATE(2010,7,9)))
    I'm looking for "Site Visit" in column B, and want the inspection date to be earlier than July 9, 2010.

    Can you please help?


  22. Hi Ivy

    I suggest that you replace "<="&DATE(2010,7,9) with <=DATE(2010,7,9). I hope the formula will work after that.

    Since dates are numeric numbers you do not require to convert them into string/text for comparision purpose.

    Regards//Yogesh Gupta

  23. Hi Yogesh,

    Hope Yogesh hope you can help with this. I've tried using Sum with Vlook-up but didn't help.

    I want to do conditional sum on the value based in column A. For example I've got 4 columns; Col A with name and Col B to Col D with number values. For all values corresponding to Name "A" in Col A i want to sum numbers in Col B to Col D. Can you pls help me with this. Pls see example below if that provides any further clarity:

    Name Value 1 Value 2 Value 3
    A 8 8 8
    A 2 7 3
    A 4 9 1
    B 4 8 7
    B 3 7 2
    C 1 7 4
    A 2 1 7
    B 2 3 6
    B 4 6 4
    C 5 4 9
    C 3 9 8

    Many thanks for your help in advance.

  24. @Amita,

    Are you looking for a **single** formula that gives the grand total of the cells in B:D for each row in which Column A cells contain the letter "A"? If so, try this formula...


    Rick Rothstein (MVP - Excel)

  25. Hi Yogesh,

    I was looking for the "countif multiple options" and stumbled upon your blog after going through several other solutions and this is where I stopped.

    Thank you for sharing.


  26. Hi Yogesh,
    Great site! I have Excel 2003. I want to match dollar items in one column that are same amt where one is a credit and one is a debit (like balancing a checkbook). I am using this formula to find matches =COUNTIF($C$2:$C$1200,C2)and it is working great to find matching dollar amts in Column C. However, I want to add one more criteria. Column D is either Debit or Credit. I only want to match Debits with Credits. Currently, if I have 100.00 in C2 and 100.00 in C3, it will count as 2, regardless of what is in Column D2 and D3. I would like a formula to count only if D2 and D3 are NOT equal. So if C2 is 100.00 and C3 is 100.00 and D2 is Debit and D3 is Credit, that would be a match. Or D2 is a Credit and D3 is a Debit, also a match. But if D2 and D3 are both Debits, that is not a match. If D2 and D3 are both Credits, not a match. Can you please help? Thank you.

  27. Dear Yogesh.

    Can you please help me?

    I have a spreadhseet and i am using multiple sheets. I am using one sheet as a database, client information, name address etc, and another sheet to capture contact/activity information.

    I have already created the formual
    =COUNTIF('Mary Contact'!$C$2:$C$3000,'Basic Database'!C3)to count how many times a client is contacted (data inputted on one sheet to be counted on the main database)the formula recognises the client reference and counts how may times it is inputted on the second sheet.

    I now want to create another formula (in a different column) which does the same as the above FIRST and then count if another if column states DNA. Meaning it checks for the client ref then counts the DNA against that ref.

    I really hope you can help with this. I have tried different variations had no luck.

    I hope this meakes sense!?

    Many thanks

  28. I have a spreadsheet with almost 103,000 rows and 28 columns of data from which I'm creating multiple pivot tables.

    The rows in Column 'A' contain four values, "Big", "Sky", "Lake", "Lodge". Column 'B' contains a Customer Number, a six-digit code. Column 'C' contains the formula =COUNTIF($B$2:$B$10300,$B2), giving me a count each Customer Number. This is great, I now know if that Customer Number is a one-time or repeat customer.

    The problem is, I now need to know if that Customer Number is a repeat customer of "Big", "Sky", "Lake", or "Lodge". In other words, I need to know if they were a customer of at least two of those four.

    This formula does not work:


    Is there a formula that can return something simple as "True" or "False" - anything that will allow me to pull/sort in a pivot table - for this criteria.

    Make sense?
    Any/all help is appreciated...

  29. Hi Yogesh

    Is it possible to use to conditions in the Sumproduct formula you have mentioned? ie
    =SUMPRODUCT(($B$13:$B$28="North OR South")*($C$13:$C$28="Ram"))

    If Ram exist for two regions (North and South) I want to return the value. I used "OR" here, I know this is not correct. Can you please help me with the correct syntax.


  30. Hi Yogesh,

    I have three columns I need to count:
    Column A: "Player"
    Column B: "Play:
    column C: "Yards"

    The first two are simply counts based on criteria.
    The A column will be a player's number.
    The B column will be "r" for run, or "p" for pass or "x" for nothing attributed to the player
    The C column will be the sum of the first two criteria in the "yards" column.


  31. Hi Yogesh,

    How to use un-need symbol/formula ; if we want to exclude some criteria while using countifs.

  32. sometime when i export my data from my software in excel then i insert subtotal for date wise excel cant show summary of subtotal. i have most important of summary of data for plz. say abt this prblm.


    EXAMPLE with column : A5 , C5, E5 or rows : c3,c5,c7,c9

    COUNTIF SHOULD NOT INCLUDES B5, D5 & F5 or c2,c4,c6 ETC..

  34. Hi! I need to count with 3 different conditions. All data is in one sheet and I need to count the numbers of cases assigned to Person A (condition 1), type of cases assigned to Person A (condition 2) within a month (condition 3).

    Greatly appreciate if you can help with this.

    Thanks much!

  35. Hi,

    I need to count the number of Pass and number of Fail for a given date for the list below:

    Pass 12/12/2011
    Pass 12/13/2011
    Fail 12/12/2011
    Fail 12/13/2011
    Pass 12/12/2011
    Pass 12/12/2011

    Please advise on how to achieve this. I tried with this...=SUM((A16:A24="Passed")*(B16:B24="12/13/2011")), but not working....please help !

  36. =COUNTIF(A2:A7,"pass")

  37. Hi,

    the formula given by you =COUNTIF(A2:A7,"pass")
    =COUNTIF(A2:A7,"fail") only gives the number of pass/ to get the number of pass for a given date ?



  38. sir i want 1 to 100 numbers i want to below 40 to 30 give me formula

  39. Hi i have a table with 2 columns columns a customer name and column b id customer i want to creat a type that search on column a the name customer and give me in column c a text (the name of my saler)

  40. hi yogesh,

    Can you help me out by sharing your thoughts about the viability of using range based criterion like '>10and <12' alongwith countif ?

    Koushik Ghosh

  41. hi i have 3 A,B&C colums with different value & name. i want to count to equal & < value of Colume B to C related of Colume A. example is below for your reference.
    Q:count how many Fish (Colum A) value of B is equal & < to C.

    A B C
    Fish 1 1
    Cat 1 1
    Fish 2 1
    Bird 1 1
    Fish 1 1

  42. i want to count below columes value with out repeated same value.for example 123 is write in Colum A 3times & 245 write 2 times & 356 only 1 time & so on.... but i want that which value repeated or not. its count 1. & we take total count of said Colum in Last cell.

    Colum A

  43. I am Looking for the Output with no of cells filled in the below mentioned formula in 2007 Excel. But need to apply the formula in 2003 Excel. Kindly suggest the way forward.


    Tried in SUMPRODUCT Formula but not working....

  44. Sir

    I want to Calculate interest @2% P.M on Late Payment from Customer on Every invoices.Grace Period Should be 30 Days.Invoices Should be adjusted on FIFO Basis with Payment.Can you Help me for this.I will be grateful for this.My E mail id is


  45. Dear Yogesh,

    I want to counting in two condition for ex. =countif(a1:a5,"2<>3") in between two number but its not working, so please suggest me. my Email ID is

  46. Dear Yogesh,

    I want to counting in two condition for ex. =countif(a1:a5,"2<>3") in between two number but its not working, so please suggest me. my Email ID is

  47. Dear Yogesh
    Col.A (date) Col.B (Plan) Col.C (Code of Salesman)
    20111213 149 80001603
    20111213 149 80001603
    20111130 91 80001709
    20111130 165 80001709
    20111130 91 80001709
    20111130 91 80001709
    20111130 165 80001709
    20111129 805 80001709
    20111129 805 80001709
    20111129 805 80001709
    20111129 91 80001709
    20120131 802 80001725
    20120124 91 80001904
    20120124 91 80001904
    20120124 91 80001904
    20120118 91 80001904
    20120118 91 80001904
    20111031 805 80001904
    20111121 805 80001904
    20120130 91 80001904
    20111202 165 80001904
    20110628 179 3449177
    20110928 179 3449177
    20111031 14 3449177
    20110425 165 3868177
    20110425 75 3868177

    I want to count the number of salesman (excluding duplicates) who sold a particular plan (say 165) between 20110425 to 20111223. Kindly suggest me a formula. My Email ID is

  48. I have one I need solving.

    I have 4-columns that I want to search for a list of possible matches.

    Here's a sample:

    Here's the column, I want to search...
    A B C D
    1 -95A0 -95A0 NONE 11H3C
    2 -95A0 -95A0 NONE 11F3Q
    3 -95A0 -95A0 NONE 62E3E
    4 -90G0 -90G0 NONE

    I want to countif (or whatever) everycell above for any cell that has the following...
    A B
    10 11B 46P
    11 11E 46S
    12 11F 47S
    13 11G 48A

    Example: A12 (11F) is found in D2 (11F3Q)--this should count as 1.

    Any suggestions?--Much appreciated in advance.

    1. Addendum: I would like to avoid a long formula. It seems to work well on individual cell criteria [ex: =COUNTIF(BB2:BD586,BD631&"*")], but I would like to do something like this: =COUNTIF(BB2:BD586, BC591&"*":BD631&"*"), which of course doesn't work this way.

  49. Hello--
    I am desperate for a formula that will find matches when the first 11 digits in a cell equal the last 11 digits in a cell, in the same column. Like this:

    Cell A

    I hope you can help. Thanks!

    1. =IF(LEFT(A1;11)=RIGHT(A1;11);"11 char in beginning and end match"; "No match")
      You'd like also to keep the cell format as Text, since numbers will convert your long number to exponential presentation and there formula will not work.

  50. Hi!

    Need help with COUNTIF or COUNTIFS, I have a data where I need to count the no. of entries in a column which are for a particular month, say for March so what should be the formula for this.

    Thanks & Regards,
    Pritish Kamal

  51. Hi!

    Need help with COUNTIF or COUNTIFS, I have a data where I need to count the no. of entries in a column which are for a particular month, say for March so what should be the formula for this.

    Thanks & Regards,
    Pritish Kamal

  52. Hello Yogesh, can you help me figure out why I cannot get this to work?

    If I enter the formula in this fashion, it works fine, but now when I want to take the value from a cell that can change.

    1. Pls enter it like this to work


      You need to join the text string with the cell reference rather than just putting it in the text string. The way you have used it excel does not recognized it as cell reference.

  53. Hello Yogesh,

    Can you please Help me Regd this........

    Conversion of Numeric to Character in Excel 2003 without writing any code by applying only formula.

    Ram Prasad

    1. Hi Ram

      Not clear about your request, can you give some example of what do you need.


  54. Small Note: SUMPRODUCT formula will not work for entire column.
    Example: =SUMPRODUCT(($B:$B="North")*($C:$C="Ram"))
    The above formula will give a error.

    Instead use a cell range as suggested by Yogesh.


  55. Hello Yogesh,

    can you help me...

    What is the formula for my situasion:

    A A A A A - 5A (summaries: all A)
    A B A A A - 4A 1B (summaries: at least 1B)
    A B B B B - 1A 4B (summaries: at least 1B)
    A B B A C - 2A 2B 1C (summaries: at least 1C)
    B C D B B - 3B 1C 1D (summaries: at least 1D)
    E E E E E - 5E (summaries: all E)

    What is the formula to count:
    all A - 1 person
    at least 1B - 2 person
    at least 1C - 1 person
    at least 1D - 1 person
    all E - 1 person

    and so on

  56. Hi
    I have data for 5 Teams - A,B,C,D,E and each team has 10 members. Now I would like to have a formulae to calualate, No of present "P" within each team. No of members who have >7:30, <7:00>7:30 staffed hours ..


  57. .Thanks Yogesh,
    It's so helpful!!!


  58. Hi Dears

    how can I use countifs with three column on of them has two text criteria

    i tried this one but it not work
    =COUNTIFS(H2:H223,COUNTIF(H2:H223,"RFT")+COUNTIF(H2:H223,"On air"))

    appreciate your kind support

  59. Hi Dears

    how can I use countifs with three column on of them has two text criteria

    i tried this one but it is not work:
    =COUNTIFS(H2:H223,COUNTIF(H2:H223,"RFT")+COUNTIF(H2:H223,"On air"))

    appreciate your kind support

  60. Hello,

    I am using this formula:

    I also have PP as a value and it is counting this. I only want to count the P. Help???

    1. Use This Formula


  61. Thanks a lot. I was struggling with multiple criteria function for the last 3 days. Since I am using MS Office 2003, I was unable to use the "COUNTIFS" function.
    I sought help from here and it really worked for me.
    Thanks a lot again.

    1-Sep-14 AK-290 29,513.00 CHEQUE
    1-Sep-14 AK-291 5,000.00 CASH
    1-Sep-14 AK-295 2,000.00 CASH
    1-Sep-14 AK-296 658,694.00 CHEQUE

    HI SIR


  63. Fabulous, what a blog it is! This weblog provides helpful data to us, keep it up. itunes login account

  64. Really cool post, highly informative and professionally written and I am glad to be a visitor of this perfect blog, thank you for this rare info!
    MS office course in chennai

  65. Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating.
    Web Design Training

  66. Thanks for sharing this information.
    Want to Buy Step Down Transformer or Are you Curious about What is Step Down Transformer? Read the blog to get your queries resolved before making a purchase.

  67. Taldeen is one of the best plastic manufacturing company in Saudi Arabia. They are manufacturing Handling Solutions Plastic products like Plastic Pallets and plastic crates. Here is the link of the product
    Handling Solutions
    Plastic Pallets
    Here is the details of best BSc Medical Imaging Technology Colleges in Bangalore. You can get the college details from the below link. BSc Medical Imaging Technology Course is one of the best demanding course in recent times in India
    BSc Medical Imaging Technology Colleges In Bangalore
    Christian College Bangalore providing BSc Medical Imaging Technology Course. Here is the link about the details of BSc Medical Imaging Technology. You can click the below link for more information about BSc Medical Imaging Technology.
    BSc Cardiac Care Technology Colleges In Bangalore
    Christian College Bangalore providing BSc Optometry Course. Here is the link about the details of BSc Optometry. You can click the below link for more information about BSc Optometry. BSc Optometry is one of the most demanding course in recent times.
    Optometry Colleges In Bangalore
    BBA Aviation course is the best (Most Demanded) management course in India. Here, Christian College Bangalore providing BBA Aviation course. You can get the details of Christian College BBA Aviation from the below mentioned link. If you are interested in BBA Aviation, just visit the below link to know about BBA Aviation.
    BBA Aviation Colleges In Bangalore
    GrueBleen is one of the Branding and Marketing agency Based in Riyadh- Saudi Arabia. The main functions of GrueBleen is Advertising, Branding, Marketing, Office Branding, Exhibition Management and Digital Marketing. Visit the below link to know more about GrueBleen Creative Club.
    Branding Agency Riyadh
    Marketing Agency Riyadh
    Agriculture Solutions – Taldeen is a plastic manufacturing company in Saudi Arabia. They are manufacturing agricultural plastic products like greenhouse cover and hay cover. Visit the below link to know more details
    Agriculture Solutions
    Greenhouse Cover
    Medical Imaging Technology – One of the most demanding allied health science course in recent times in India. Check out the details of Best BSc Medical Imaging Technology Colleges Details with the following link.
    BSc Medical Imaging Technology Colleges In Bangalore
    BSc Perfusion Technology – If you are looking to study BSc Perfusion Technology in Bangalore, just check out the following link. In that link you can get the details of Best BSc Medical Imaging Technology colleges in Bangalore
    BSc Perfusion Technology Colleges in Bangalore
    GrueBleen – One of the best social media marketing agency in Riyadh- Saudi Arabia. Visit here for the all service details of GrueBleen.
    Social Media Marketing Agency


  68. You write this post very carefully I think, which is easily understandable to me. Not only this, but another post is also good. As a newbie, this info is really helpful for me. Thanks to you.
    Tally ERP 9 Training
    tally classes
    Tally Training institute in Chennai
    Tally course in Chennai

  69. Mukul Sharma   When the film “Birds of Prey” was released on 07 Feb 2020, trade pundits projected it to gross $50 to $55 million during the opening weekend in the US and Canadian markets. Warner Bros, the distributors of the film had their own projection pegged at $45 million. However, It could muster only &hellip.

  70. Thanks for the interesting blog that you have implemented here. Very helpful and innovative. Waiting for your next upcoming article.
    Digital Marketing Course In Kolkata