countif
 

hello,
i am trying to write following formula, but.............

sheet 1 Sheet 2
A_col B_col A_col B_col C_col
japan 10 japan 10 formula would be here
(C1)
korea 40 london 35
japan 10 tokyo 22

=countif('sheet 1'!a1:a20='sheet2'!=a1)*('sheet 1'!b1:b20='sheet 2'!b1))

but this formula doesn't work, acctully i want count like Japan = 10 from
sheet-1 into sheet-2.
Thanks in advance.

countif
 

Hi

Try
=SUMPRODUCT((Sheet1!$A$2:$A$20=$A2)*(Sheet1!$B$2:$ B$20=$B2))

--
Regards

Roger Govier


"Tufail" wrote in message
...
hello,
i am trying to write following formula, but.............

sheet 1 Sheet 2
A_col B_col A_col B_col C_col
japan 10 japan 10 formula would be
here
(C1)
korea 40 london 35
japan 10 tokyo 22

=countif('sheet 1'!a1:a20='sheet2'!=a1)*('sheet 1'!b1:b20='sheet
2'!b1))

but this formula doesn't work, acctully i want count like Japan = 10
from
sheet-1 into sheet-2.
Thanks in advance.

countif
 

Hi,

Try...

=SUMPRODUCT((Sheet1!$A$1:$A$20=Sheet2!A1)*(Sheet1! $B$1:$B$20=Sheet2!B1)*(Sheet1!$A$1:$A$20<""))

Or, if you're not worried about blank cells being tested for, then
just...

=SUMPRODUCT((Sheet1!$A$1:$A$20=Sheet2!A1)*(Sheet1! $B$1:$B$20=Sheet2!B1))

would do.

Ken Johnson