Hi,
I have two seperate data in two seperate columns. say columns are
ABC 123
DEF 456
GHI 789
JKL 012

Now i wish to write a formula that will count the occurance of 123 with
corrosponde to ABC. say for above columns, value would be "1".

i am trying it with COUNTIF, but i am not able to concatenate two COUNTIF's.

Please help

Mukesh

Hi!

Try this:

=SUMPRODUCT(--(A1:50="ABC"),--(B1:B50=123))

Better to use cells to hold the criteria:

D1 = ABC
E1 = 123

=SUMPRODUCT(--(A1:50=D1),--(B1:B50=E1))

Biff

"Mukesh Garg" <Mukesh wrote in message
...
Hi,
I have two seperate data in two seperate columns. say columns are
ABC 123
DEF 456
GHI 789
JKL 012

Now i wish to write a formula that will count the occurance of 123 with
corrosponde to ABC. say for above columns, value would be "1".

i am trying it with COUNTIF, but i am not able to concatenate two
COUNTIF's.

Please help

Mukesh

Dear Biff,

It is not returning the right value. In my sheet according to cell
information value should be 5 but it is returning "0".

Would this formula wrk???

Suggest

Thanks
Mukesh

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(A1:50="ABC"),--(B1:B50=123))

Better to use cells to hold the criteria:

D1 = ABC
E1 = 123

=SUMPRODUCT(--(A1:50=D1),--(B1:B50=E1))

Biff

"Mukesh Garg" <Mukesh wrote in message
...
Hi,
I have two seperate data in two seperate columns. say columns are
ABC 123
DEF 456
GHI 789
JKL 012

Now i wish to write a formula that will count the occurance of 123 with
corrosponde to ABC. say for above columns, value would be "1".

i am trying it with COUNTIF, but i am not able to concatenate two
COUNTIF's.

Please help

Mukesh

Hi!

Try this:

=SUMPRODUCT(--(A1:50="ABC"),--(B1:B50="123"))

If that works then your 123 is really TEXT and is not a numeric number. If
that doesn't work post the *EXACT* formula that you tried.

Biff

"Mukesh Garg" wrote in message
...
Dear Biff,

It is not returning the right value. In my sheet according to cell
information value should be 5 but it is returning "0".

Would this formula wrk???

Suggest

Thanks
Mukesh

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(A1:50="ABC"),--(B1:B50=123))

Better to use cells to hold the criteria:

D1 = ABC
E1 = 123

=SUMPRODUCT(--(A1:50=D1),--(B1:B50=E1))

Biff

"Mukesh Garg" <Mukesh wrote in message
...
Hi,
I have two seperate data in two seperate columns. say columns are
ABC 123
DEF 456
GHI 789
JKL 012

Now i wish to write a formula that will count the occurance of 123 with
corrosponde to ABC. say for above columns, value would be "1".

i am trying it with COUNTIF, but i am not able to concatenate two
COUNTIF's.

Please help

Mukesh

Hey Biff....

that works....

thanks a lot...

mukesh

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(A1:50="ABC"),--(B1:B50="123"))

If that works then your 123 is really TEXT and is not a numeric number. If
that doesn't work post the *EXACT* formula that you tried.

Biff

"Mukesh Garg" wrote in message
...
Dear Biff,

It is not returning the right value. In my sheet according to cell
information value should be 5 but it is returning "0".

Would this formula wrk???

Suggest

Thanks
Mukesh

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(A1:50="ABC"),--(B1:B50=123))

Better to use cells to hold the criteria:

D1 = ABC
E1 = 123

=SUMPRODUCT(--(A1:50=D1),--(B1:B50=E1))

Biff

"Mukesh Garg" <Mukesh wrote in message
...
Hi,
I have two seperate data in two seperate columns. say columns are
ABC 123
DEF 456
GHI 789
JKL 012

Now i wish to write a formula that will count the occurance of 123 with
corrosponde to ABC. say for above columns, value would be "1".

i am trying it with COUNTIF, but i am not able to concatenate two
COUNTIF's.

Please help

Mukesh