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

Page Updates

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. The written steps are on the Contextures COUNT Functions page.

There is a complete transcript of the tutorial directly beneath the video.

VIDEO TRANSCRIPT

Counting With Criteria

In Excel you can count using criteria with the COUNTIF function.

In later versions of Excel (2007 and later) you can count multiple criteria with the COUNTIFS function.

So here we have a list of items that we’ve sold and the quantity for each.

We would like to find the number of orders where a pen was the item sold and the quantity is greater than 10. 

First Criteria

So in this cell I’m going to start with an equal sign and then type COUNTIFS an open bracket and the first thing I’m going to check is the item that was sold. 

The range, first range is A2:A10. Then I’ll type a comma and the criteria for that range I’m just going to type in here inside double quotes pen, and then another comma.

That’s the first thing we’re going to check, is what item was sold.

Additional Criteria

Next will be the quantity so I’ll select the range that has the quantities, another comma and we want quantity greater than or equal to 10 so within double quotes

I’ll do a greater than symbol, equal, and a 10, then another double quote

Close the bracket and press Enter. There were two orders for pen where the quantity is greater than 10. Instead of typing these criteria in here I can refer to a cell. 

So instead of typing pen inside double quotes, I could click on a cell where I have typed the word pen. The same for this criteria for the quantity

I’m going to take out the 10 just by deleting that, leaving the operators within the double quotes. 

Adjustments

Then I’ll type an ampersand and the cell that has the number. So this is greater than or equal to whatever number is in cell E3.

When I press ENTER I get the same result. It’s just easier to change. 

Then I could type a 5 here now, and we see that there were 4 orders where the quantity is greater than or equal to 5 instead of the 10 that we had in there before.

So this formula is much more flexible if you use cell references, rather than typing the values in as hard-coded values.

End Of Transcript

_________

21 thoughts on “Use Excel COUNTIFS to Count With Multiple Criteria”

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

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

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

  4. 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…

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

    1. Help me with a formular,in one formular from a large spread sheet, to find how many times text appear in a column where there are 10 separate text values and I would like to count the number of times each appears in that column in as simple a formular as possible without using a pivot table. I appreciate the help from you.I am business man and don’t have the time to learn advanced excel.

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

  7. Thank you so much for posting this video!
    I am working on a troubleshooting spreadsheet and needed to know how many cases were troubleshooting (as vs. billing) and then of which type. This will save me a ton of headaches.

  8. In Excel, can I include a range of dates in a formula? For Example, (Sept.3 through Dec.21) as 1st quarter?

  9. Hi, could anyone help me trying to work out a way to avoid using Countifs in the following way:
    =COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018109402″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018109396″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018106869″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018106852″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018106876″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018106845″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018106685″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018106937″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018106753″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018106920″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018112129″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018112112″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018112136″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018118763″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018118749″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018118725″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018118756″,s!$Q:$Q,”>=0″)
    +COUNTIFS(s!$A:$A,D$1&”*”,s!$G:$G,”9318018118787″,s!$Q:$Q,”>=0″)
    Huge etcetera…
    Basically I get those barcodes from column G in a tab called “S” and each week I need to add new barcodes to the formula but adding a new line is not ideal as I will potentially reach the limit of characters that can be used per formula per cell. My question is, could I possibly combine Countifs with vlookup or get Countifs to get those barcodes from a specified range of cells where I could easily dump more barcodes?
    Thanks heaps in advance!

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.