Multiple Criteria & Count if Equals 0
 

Need help with a formula to find criteria in one column then count the number
of 0's in another.
Example:

col a col b
smith 2
jones 9
smith 0
rider 4
smith 0
smith 0

need the return for the number of 0's for smith
this would equal 3

using excel2007
help is much appreciated

Multiple Criteria & Count if Equals 0
 

Try this

=SUMPRODUCT((A1:A6="Smith")*(B1:B6=0))

Mike

"slf" wrote:

Need help with a formula to find criteria in one column then count the number
of 0's in another.
Example:

col a col b
smith 2
jones 9
smith 0
rider 4
smith 0
smith 0

need the return for the number of 0's for smith
this would equal 3

using excel2007
help is much appreciated

Multiple Criteria & Count if Equals 0
 

Try
=SUMPRODUCT((A1:A10="Smith")*(B1:B10=0)*(B1:B10<" "))

If this post helps click Yes
---------------
Jacob Skaria


"slf" wrote:

Need help with a formula to find criteria in one column then count the number
of 0's in another.
Example:

col a col b
smith 2
jones 9
smith 0
rider 4
smith 0
smith 0

need the return for the number of 0's for smith
this would equal 3

using excel2007
help is much appreciated

Multiple Criteria & Count if Equals 0
 

Mike,
Thank you -
I had the "Sumproduct" but was unable to finish it. The * never occurred to
me.


"Mike H" wrote:

Try this

=SUMPRODUCT((A1:A6="Smith")*(B1:B6=0))

Mike

"slf" wrote:

Need help with a formula to find criteria in one column then count the number
of 0's in another.
Example:

col a col b
smith 2
jones 9
smith 0
rider 4
smith 0
smith 0

need the return for the number of 0's for smith
this would equal 3

using excel2007
help is much appreciated

Multiple Criteria & Count if Equals 0
 

Hi,

Since you are using Excel 2007, you may also use this formula

=COUNTIFS(a5:a10,a12,b5:b10,0)

A12 has Smith

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"slf" wrote in message
...
Need help with a formula to find criteria in one column then count the
number
of 0's in another.
Example:

col a col b
smith 2
jones 9
smith 0
rider 4
smith 0
smith 0

need the return for the number of 0's for smith
this would equal 3

using excel2007
help is much appreciated