Excel Count With Multiple Criteria - COUNTIFS Function
When you need an Excel count With multiple criteria, use Excel's COUNTIFS function. This video shows you the steps.
For example, count orders where a pen is the item ordered, and the quantity is greater than or equal to 10.
Put the criteria in cells, where it's easy to change them later.
Get the free sample file from my Contextures site, and follow along with the video.
http://www.contextures.com/xlfunction...
TRANSCRIPT for Excel Count With Multiple Criteria - COUNTIFS Function
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.
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.
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.
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.