Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct vs. countif | Excel Discussion (Misc queries) | |||
Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions |