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.
=SUMPRODUCT(($B$13:$B$28="North")*($C$13:$C$28="Ram"))
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

www.yogeshguptaonline.com


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

.
Posted by Yogesh Gupta at 6:57 PM
Labels: , , , , , ,
Reactions: 

43 comments:

  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.
    ReplyDelete
  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()
    Loop
    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.

    Thanks.
    ReplyDelete
  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.
    ReplyDelete
  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
    =SUMPRODUCT((Test!$B$13:$B$28="North")*(Test!$C$13:$C$28="Ram"))

    This formula will work in Excel 2003 and 2007 both.

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

    =COUNTIFS(Test!$B$13:$B$28,"North",Test!$C$13:$C$28,"Ram")

    Thanks
    Yogesh Gupta
    ReplyDelete
  5. hi yogesh, plz help me in this..
    Names
    raam
    raam
    raam
    shaam,raam
    shaam,raam
    raam

    now i need count of rows containing raam i.e.6
    and count of rows containing shaam i.e.2
    ReplyDelete
  6. @karandeep,

    You can do this with the COUNTIF function...

    =COUNTIF($A$1:$D$100,"*raam*")
    =COUNTIF($A$1:$D$100,"*shaam*")

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

    =COUNTIF($A$1:$D$100,"*"&E1&"*")
    ReplyDelete
  7. Mr. Gupta,
    THANK YOU.
    I am bookmarking your site and will be back often.
    Sincerely,
    Chip Dempsey
    ReplyDelete
  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
    ReplyDelete
  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!
    ~Nicolle
    ReplyDelete
  10. Hi Yogesh, can u pls tell me on how to count multiple opbects in a cell?
    ReplyDelete
  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.
    ReplyDelete
  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
    Shabry
    ReplyDelete
  13. Hi Yogesh,

    Sorry its SUMPRODUCT, not SUBPRODUCT
    ReplyDelete
  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
    ReplyDelete
  15. Hi Yogesh,

    I'll send the file by email.

    Thanks
    Shabry
    ReplyDelete
  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..

    Regards
    Ganesh
    ReplyDelete
  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
    ReplyDelete
  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?

    Thanks
    ReplyDelete
  19. @Yossi,

    Give this formula a try...

    =SUMPRODUCT((A1:A100="John")*((B1:B100="Smith")+(B1:B100="Apple")))

    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)
    ReplyDelete
  20. This comment has been removed by the author.
    ReplyDelete
  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?

    Thanks!
    Ivy
    ReplyDelete
  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
    ReplyDelete
  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.
    ReplyDelete
  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...

    =SUMPRODUCT((A1:A100="A")*(B1:B100+C1:C100+D1:D100))

    Rick Rothstein (MVP - Excel)
    ReplyDelete
  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.

    Martin
    ReplyDelete
  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.
    ReplyDelete
  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
    ReplyDelete
  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:

    =PRODUCT(COUNTIFS(A$2:A$10300,{"Big","Sky","Lake","Lodge"},B$2:B$10300,B2))>0


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

    Thanks
    Ram.M
    ReplyDelete
  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.

    Thanks,
    ReplyDelete
  31. Hi Yogesh,

    How to use un-need symbol/formula ; if we want to exclude some criteria while using countifs.
    ReplyDelete
  32. sometime when i export my data from my software in excel then i insert subtotal for date wise total..bt excel cant show summary of subtotal. i have most important of summary of data for work..so plz. say abt this prblm.
    ReplyDelete
  33. HI I NEED TO COMPARE ALTERNATE CELLS WITH COUNTIF PLS HELP

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

    COUNTIF SHOULD NOT INCLUDES B5, D5 & F5 or c2,c4,c6 ETC..
    ReplyDelete
  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!
    ReplyDelete
  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 !
    ReplyDelete
  36. =COUNTIF(A2:A7,"pass")
    =COUNTIF(A2:A7,"fail")
    ReplyDelete
  37. Hi,

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

    Regards,

    Sanjay.
    ReplyDelete
  38. sir i want 1 to 100 numbers i want to below 40 to 30 give me formula
    ReplyDelete
  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)
    ReplyDelete
  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
    ReplyDelete
  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
    ReplyDelete
  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
    123
    123
    245
    245
    123
    356
    ReplyDelete
  43. This comment has been removed by the author.
    ReplyDelete

Subscribe to: Post Comments (Atom)