Query for data in an array
 

I have data that is arranged as below
Column Name Col1 Col2 Col3
a b s
a c w
b d s
b c w
a d w

I want to count number of s in col3 where col1 = a and
col2 = b

I am trying to use array formula using countif and if
functions, but i am getting value#

Can some one tell me the right way to get the result

thanx

Query for data in an array
 

{=SUM(IF((A6:A10="a")*(B6:B10="b")*(C6:C10="s"),1, 0))}

"anonymous" wrote in message
...
I have data that is arranged as below
Column Name Col1 Col2 Col3
a b s
a c w
b d s
b c w
a d w

I want to count number of s in col3 where col1 = a and
col2 = b

I am trying to use array formula using countif and if
functions, but i am getting value#

Can some one tell me the right way to get the result

thanx

Query for data in an array
 

=sumproduct((a2:a22="a")*(b2:b22="B")*(c2:c22="S") )
Does NOT have to be array entered

--
Don Guillett
SalesAid Software

"anonymous" wrote in message
...
I have data that is arranged as below
Column Name Col1 Col2 Col3
a b s
a c w
b d s
b c w
a d w

I want to count number of s in col3 where col1 = a and
col2 = b

I am trying to use array formula using countif and if
functions, but i am getting value#

Can some one tell me the right way to get the result

thanx