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]

I am confused as to what you want???

Do you want to the result to display "Already entered" in Rows in Column I of workbook 2 if you enter 200 (As 200 is multiple times entered in workbook 1) or you want to enter 200-Count (Total Count of 200 in Workbook 1 and Count of 200 in workbook 2)

Please post a sample of desired result

 

[lyc]

use countifs: sample formula: =COUNTIFS($G$10:$G$17,Q3,$I$10:$I$17,R3)

 

[etaf]

Is this other workbook closed
Are you using version 2007 of excel sas in profile ?

if so try
=COUNT(IF('path to work book/[workbook1.xlsx]Sheet1'!$c:$c='path to work book/[workbook2.xlsx]Sheet1'!j6,1))

where c is the column to look up the value and j6 is the value you are looking for

 

[nabeelahmed]

I am confused as to what you want???

Do you want to the result to display "Already entered" in Rows in Column I of workbook 2 if you enter 200 (As 200 is multiple times entered in workbook 1) or you want to enter 200-Count (Total Count of 200 in Workbook 1 and Count of 200 in workbook 2)

Please post a sample of desired result

 

[nabeelahmed]

Dear All friends, thanks for all of you for the reply.. I think i could't explain what i desire.. so i am sending new sample shot below

In workbook1 column"C" 200 is entered 6 times and for the same count is showing in column"B"
I want that when i will work in workbook2 and will enter the same serial number in column"J" , in column "I" the count should be shown 200-7

my excel version ( Office 365 Business)
one workbook might be open at a time

 

[CA_Punit]

How about

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

Cell Formulas
Range	Formula
G3	G3	=H3&"-"&MAX(IFERROR(MID(B3:B17,SEARCH(H3,B3:B17)^0*SEARCH("-",B3:B17)+1,999)+0,0))+1

 

[CA_Punit]

One more question
What should happen if i insert 200 twice in Column H.
What result should i get????

 

[nabeelahmed]

One more question
What should happen if i insert 200 twice in Column H.
What result should i get????

Dear Please see below.. Hope you will get it?

 

[nabeelahmed]

Hi Friends, Can somebody help me on below query?