Use Excel COUNTIFS to Count With Multiple Criteria

In Excel 2007 and Excel 2010, you can use the new COUNTIFS function to count, based on multiple criteria. For example, in a list of orders, you can find out how many orders were for pens, and had a quantity of 10 or more.

countifs01

I have updated the Contextures COUNT Functions page, to include a COUNTIFS example, and video demo. If you are using Excel 2003, or earlier versions, you can use the SUMPRODUCT function instead. There is an example for that function on the COUNT page too.

Watch the COUNTIFS Video

To see the steps for using the COUNTIFS function, you can watch this short Excel video tutorial.

_________

12 comments to Use Excel COUNTIFS to Count With Multiple Criteria

  • m-b

    What new features does Excel 20007 have? Sorry couldn't resist :-)

  • Seth

    I need to pull out a multiple criteria from a list of 3,000 companies & individuals. I have used the function =IF(SUM(COUNTIF(A2,{"*Dr.*","*Drs.*","*Dr *","*Drs*"}))>0,"Yes","No") to pull out Doctors. In the function I leave a space after "*Dr *", so that the results do not include words like "Drive"etc.

    I used the FIND function to pull out individuals(",",A2:A3000,1). I use the results from 1-14 in the FIND function to find the individuals. The individuals in the list always have a common after their last name so the find function works.

    How do I combine the two functions to produce the results in one column.

    Thank you

  • Jason D

    Excellent. This saved me an hour of work.
    Thank you!

  • sumit

    this is a good video for countifs function

  • Haitham

    Dear,
    It will already appreciated if u help me in doing contifs function with three columns one of them with two text criteria.

    Many thanks in advance

  • Rambabu Jalli

    Thanks for an excellent video!

  • Charles

    How do I sum up an A, D, and T when using =countif

  • Harshit D Jhaveri

    This helped me get out of a jam. Thank you!! I learnt the Sumifs function as a corollary to the Countifs function. Thank you, Debra

  • Dee

    How do I count a column of text with a criteria of dates, for example column A list Calls A B C and col B list the dates how do I get the count for all A calls in the month of October. The dates are 10/1/2013 etc...

  • Robert Manning

    I'm counting the occurrence of multiple string values in a range. This works:

    =COUNTIF(RiskRegister!T10:T64,"1A")+COUNTIF(RiskRegister!T10:T64,"1B")+COUNTIF(RiskRegister!T10:T64,"1C")+COUNTIF(RiskRegister!T10:T64,"1D")+COUNTIF(RiskRegister!T10:T64,"2A")+COUNTIF(RiskRegister!T10:T64,"2B")+COUNTIF(RiskRegister!T10:T64,"3A")

    However, I must first check and make sure that I'm only checking the rows that match the project number selected in another tab. For example:

    =COUNTIFS(RiskRegister!D10:D64,KPIs!B2

    My problem is that I'm not sure how to combine these two.

    Thanks for any help,

    Bob

  • Benelin

    Items price

    pen 2
    pencil 5
    Book 7
    pen 11
    pencil 3
    pen 12
    bag 1
    pen 23

    In the above table help me to write an excel formula to find out how many pen are there with price greater than 11 but less than 20.Thanks in advance.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>