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.
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.
_________
What new features does Excel 20007 have? Sorry couldn't resist :-)
Oops! I was seeing a bit too far into the future. ;-)
Thanks, it's fixed now.
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
Excellent. This saved me an hour of work.
Thank you!
this is a good video for countifs function
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