Countif( 2 criteria = true ?)
 

Hi can you please help me...

I am trying count the number of items based on 2 criteria can you please
help me..?
Basically I have a sheet and in col A I have Code and col B I have name. eg:
Col A - Col B
1001 - Mike
1001 - Dave
1001 - Keith
1002 - Terry
1001 - Mike
What I am trynig to do is count how many say ("Mike" col B) with code of say
("1001" col A). so the answer according to the above will be "2".
I know I can use the dsum function to do this but the problem is I am trying
to get a formula in 1 cell to do the job as opposed to 2 rows + 3 columns..
hense is there a kind of countif(and) function or something I can use ???
Thanks.

Countif( 2 criteria = true ?)
 

=SUMPRODUCT(--(B2:B200="Mike"),--(A2:A200=1001))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MyKool" wrote in message
...
Hi can you please help me...

I am trying count the number of items based on 2 criteria can you please
help me..?
Basically I have a sheet and in col A I have Code and col B I have name.
eg:
Col A - Col B
1001 - Mike
1001 - Dave
1001 - Keith
1002 - Terry
1001 - Mike
What I am trynig to do is count how many say ("Mike" col B) with code of
say
("1001" col A). so the answer according to the above will be "2".
I know I can use the dsum function to do this but the problem is I am
trying
to get a formula in 1 cell to do the job as opposed to 2 rows + 3
columns..
hense is there a kind of countif(and) function or something I can use ???
Thanks.

Countif( 2 criteria = true ?)
 

Thanks thats great !!!

Please for my own knowledge, can you please tell me what is the double "-"
is for ??? as in :
= sumproduct(-- <-what is this for or what does it do ????

Many thanks

"Bob Phillips" wrote:

=SUMPRODUCT(--(B2:B200="Mike"),--(A2:A200=1001))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MyKool" wrote in message
...
Hi can you please help me...

I am trying count the number of items based on 2 criteria can you please
help me..?
Basically I have a sheet and in col A I have Code and col B I have name.
eg:
Col A - Col B
1001 - Mike
1001 - Dave
1001 - Keith
1002 - Terry
1001 - Mike
What I am trynig to do is count how many say ("Mike" col B) with code of
say
("1001" col A). so the answer according to the above will be "2".
I know I can use the dsum function to do this but the problem is I am
trying
to get a formula in 1 cell to do the job as opposed to 2 rows + 3
columns..
hense is there a kind of countif(and) function or something I can use ???
Thanks.

Countif( 2 criteria = true ?)
 

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MyKool" wrote in message
...
Thanks thats great !!!

Please for my own knowledge, can you please tell me what is the double "-"
is for ??? as in :
= sumproduct(-- <-what is this for or what does it do ????

Many thanks

"Bob Phillips" wrote:

=SUMPRODUCT(--(B2:B200="Mike"),--(A2:A200=1001))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MyKool" wrote in message
...
Hi can you please help me...

I am trying count the number of items based on 2 criteria can you
please
help me..?
Basically I have a sheet and in col A I have Code and col B I have
name.
eg:
Col A - Col B
1001 - Mike
1001 - Dave
1001 - Keith
1002 - Terry
1001 - Mike
What I am trynig to do is count how many say ("Mike" col B) with code
of
say
("1001" col A). so the answer according to the above will be "2".
I know I can use the dsum function to do this but the problem is I am
trying
to get a formula in 1 cell to do the job as opposed to 2 rows + 3
columns..
hense is there a kind of countif(and) function or something I can use
???
Thanks.