Need the correct formula
 

I am trying to count all the cells in a table that have a Category (first
column)of 'data' and a Priority (second column) of 'medium'. I can get a
count of all the cells with a value of 'medium' by using the 'CountIf'
function. But I can't seem to further restrict the count to just 'data' and
'medium'. I've tried the If-And function without sucess.

Thanks in advance
--
Rob K

Need the correct formula
 

=sumproduct(--(a1:a10="Data"),--(b1:b10="medium"))

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

======
And if you're using xl2007, you may want to look at: =countifs()



Rob K wrote:

I am trying to count all the cells in a table that have a Category (first
column)of 'data' and a Priority (second column) of 'medium'. I can get a
count of all the cells with a value of 'medium' by using the 'CountIf'
function. But I can't seem to further restrict the count to just 'data' and
'medium'. I've tried the If-And function without sucess.

Thanks in advance
--
Rob K

--

Dave Peterson

Need the correct formula
 

On 9 May, 23:30, Rob K wrote:
But I can't seem to further restrict the count to just 'data' and
'medium'. I've tried the If-And function without sucess.

Thanks in advance
--
Rob K

Col A.........ColB
Category....Priority

Add ColC
=A1&B1

This will concatenate the two values together. Copy this formula down.

Now you can COUNTIF... using 'datamedium'

HTH

Andrew

Need the correct formula
 

=Sumproduct(--(Category="Data"),--(Priority="Medium))

where Category and Priority are the range references for your two columns.

"Rob K" wrote:

I am trying to count all the cells in a table that have a Category (first
column)of 'data' and a Priority (second column) of 'medium'. I can get a
count of all the cells with a value of 'medium' by using the 'CountIf'
function. But I can't seem to further restrict the count to just 'data' and
'medium'. I've tried the If-And function without sucess.

Thanks in advance
--
Rob K

Need the correct formula
 

The concat idea worked. Thank you very much!
--
Rob K


"loudfish" wrote:

On 9 May, 23:30, Rob K wrote:
But I can't seem to further restrict the count to just 'data' and
'medium'. I've tried the If-And function without sucess.

Thanks in advance
--
Rob K

Col A.........ColB
Category....Priority

Add ColC
=A1&B1

This will concatenate the two values together. Copy this formula down.

Now you can COUNTIF... using 'datamedium'

HTH

Andrew