Countif Formula with multiple criteria

[nabeelahmed]

Dear Friends,

I am trying to use Countif for multiple criteria but unfortunately not able to do, i am able to use only for one criteria for workbook1 [ =C6&"-"&COUNTIF(C$1:C6,C6) ]
Actually i have two workbooks where i am using same serial number as entered in Workbook1 column"C" and Workbook2 Column"J"
i want that when i will use same serial number in 2nd workbook which is already in used in 1st workbook should be shown that it is already entered in 1st workbook so that there will be no duplication in serial number.

Thanks

Nabeel

 

[CA_Punit]

Book1
	B	C	D	E	F	G	H	I
1	Workbook					Workbook
2
3	200-1	200				201-2	201
4	200-2	200				201-3	201
5	200-3	200				202-1	202
6	200-4	200				200-7	200
7	200-5	200
8	201-1
9	200-6
10
11
12
13
14
Sheet1

Cell Formulas
Range	Formula
G3:G6	G3	=H3&"-"&MAX(MAX(IFERROR(MID($B$3:$B$17,SEARCH(H3,$B$3:$B$17)^0*SEARCH("-",$B$3:$B$17)+1,999)+0,0)),MAX(IFERROR(MID($G$2:G2,SEARCH(H3,$G$2:G2)^0*SEARCH("-",$G$2:G2)+1,999)+0,0)))+1

 

[CA_Punit]

Use Control+Shift+Enter Instead of Just Enter

 

[nabeelahmed]

Book1
	B	C	D	E	F	G	H	I
1	Workbook					Workbook
2
3	200-1	200				201-2	201
4	200-2	200				201-3	201
5	200-3	200				202-1	202
6	200-4	200				200-7	200
7	200-5	200
8	201-1
9	200-6
10
11
12
13
14
Sheet1

Cell Formulas
Range	Formula
G3:G6	G3	=H3&"-"&MAX(MAX(IFERROR(MID($B$3:$B$17,SEARCH(H3,$B$3:$B$17)^0*SEARCH("-",$B$3:$B$17)+1,999)+0,0)),MAX(IFERROR(MID($G$2:G2,SEARCH(H3,$G$2:G2)^0*SEARCH("-",$G$2:G2)+1,999)+0,0)))+1

Dear Punit, Thank you very much for your help.. Here i have one more question if you can help me for this too. if i will have one more sheet as added below workbook2 then what will be the change in formula?

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H	I	J
1		Workbook			Workbook			Workbook
2
3		200-1	200		201-2	201		201-4	201
4		200-2	200		201-3	201		202-2	202
5		200-3	200		202-1	202
6		200-4	200		200-7	200
7		200-5	200
8		201-1
9		200-6
10
11
12
Sheet2

Cell Formulas
Range	Formula
H3:H4	H3	=I3&"-"&MAX(MAX(IFERROR(MID($B$3:$B$17,SEARCH(I3,$B$3:$B$17)^0*SEARCH("-",$B$3:$B$17)+1,999)+0,0)),MAX(IFERROR(MID($H$2:H2,SEARCH(I3,$H$2:H2)^0*SEARCH("-",$H$2:H2)+1,999)+0,0)),MAX(IFERROR(MID($E$3:$E$17,SEARCH(I3,$E$3:$E$17)^0*SEARCH("-",$E$3:$E$17)+1,999)+0,0)))+1
E3:E6	E3	=F3&"-"&MAX(MAX(IFERROR(MID($B$3:$B$17,SEARCH(F3,$B$3:$B$17)^0*SEARCH("-",$B$3:$B$17)+1,999)+0,0)),MAX(IFERROR(MID($E$2:E2,SEARCH(F3,$E$2:E2)^0*SEARCH("-",$E$2:E2)+1,999)+0,0)))+1

But i am not so convinced with the formula as i have to short it down.

I have few Question. Are the Workbooks in Seperate Sheets or in the same Sheet. If it is in the same sheet then it can be shortened.However if the Worksheet data are in different sheet it will be cumbersome process to shorten the formula

 

[nabeelahmed]

Book1
	A	B	C	D	E	F	G	H	I	J
1		Workbook			Workbook			Workbook
2
3		200-1	200		201-2	201		201-4	201
4		200-2	200		201-3	201		202-2	202
5		200-3	200		202-1	202
6		200-4	200		200-7	200
7		200-5	200
8		201-1
9		200-6
10
11
12
Sheet2

Cell Formulas
Range	Formula
H3:H4	H3	=I3&"-"&MAX(MAX(IFERROR(MID($B$3:$B$17,SEARCH(I3,$B$3:$B$17)^0*SEARCH("-",$B$3:$B$17)+1,999)+0,0)),MAX(IFERROR(MID($H$2:H2,SEARCH(I3,$H$2:H2)^0*SEARCH("-",$H$2:H2)+1,999)+0,0)),MAX(IFERROR(MID($E$3:$E$17,SEARCH(I3,$E$3:$E$17)^0*SEARCH("-",$E$3:$E$17)+1,999)+0,0)))+1
E3:E6	E3	=F3&"-"&MAX(MAX(IFERROR(MID($B$3:$B$17,SEARCH(F3,$B$3:$B$17)^0*SEARCH("-",$B$3:$B$17)+1,999)+0,0)),MAX(IFERROR(MID($E$2:E2,SEARCH(F3,$E$2:E2)^0*SEARCH("-",$E$2:E2)+1,999)+0,0)))+1

But i am not so convinced with the formula as i have to short it down.

I have few Question. Are the Workbooks in Seperate Sheets or in the same Sheet. If it is in the same sheet then it can be shortened.However if the Worksheet data are in different sheet it will be cumbersome process to shorten the formula

Data is in different sheets