Excel formula to find occurrences greater than 1

[JENWPS]

I am trying to create a formula to find out if a certain word appears more than one time in a column with criteria. I have a PO# in column A and in column B the words DROP or MISC appear. I am trying to find out if the word DROP appears more than once for each PO#. I am currently using the formula =COUNTIF(A:A,A2)=COUNTIFS(A:A,A2,B:B,"=DROP")>1 but it returns all as TRUE. In the example below, PO#'s 2401034, 2401096 and 2401284 should all come back as TRUE.

PO NUMBER	ProductLine
2401307	DROP
2401307	MISC
2401309	MISC
2401309	DROP
2401034	DROP
2401034	DROP
2401034	MISC
2401067	MISC
2401067	MISC
2401067	DROP
2401096	DROP
2401096	MISC
2401096	DROP
2401195	MISC
2401195	DROP
2401195	MISC
2401284	MISC
2401284	DROP
2401284	DROP

 

[SanjayGMusafir]

=COUNTIFS(A:A,A2,B:B,"=DROP")>1

Try

=COUNTIFS(A:A,A2,B:B,"DROP")>1

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS(A:A,A2,B:B,"DROP")>1

 

[JENWPS]

Try

=COUNTIFS(A:A,A2,B:B,"DROP")>1

That worked! Thank you!

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.

 

[JENWPS]

I have another inquiry regarding this same spreadsheet but I'm not sure if I should start a new thread. I'll ask here for now. For this same project, I have 3 columns - Column A (Vendor), Column B(PO#), Column C(Product Line). Similar to last time I am trying to weed out certain PO's that meet certain criteria. For this one, as long as all of the DROP lines for a given PO have the same vendor it can be deleted. So if a PO# has a Product Line DROP and the VENDOR is the same for each of those DROP lines I can disregard that PO all together regardless of the MISC lines.

VENDOR	PO#	PRODUCT LINE
CONTIXO	2401242	DROP	DON'T NEED
CONTIXO	2401242	DROP	DON'T NEED
CONTIXO	2401242	DROP	DON'T NEED
CONTIXO	2401242	DROP	DON'T NEED
MATTEL	2401242	MISC	DON'T NEED
KIDGAL	2401242	MISC	DON'T NEED
CONTIXO	2401246	DROP	KEEP
WONFOLD	2401246	DROP	KEEP
STANS	2401253	DROP	KEEP
ROYALG	2401253	DROP	KEEP
STANS	2401253	MISC	KEEP

 

[JENWPS]

I have another inquiry regarding this same spreadsheet but I'm not sure if I should start a new thread. I'll ask here for now. For this same project, I have 3 columns - Column A (Vendor), Column B(PO#), Column C(Product Line). Similar to last time I am trying to weed out certain PO's that meet certain criteria. For this one, as long as all of the DROP lines for a given PO have the same vendor it can be deleted. So if a PO# has a Product Line DROP and the VENDOR is the same for each of those DROP lines I can disregard that PO all together regardless of the MISC lines.

VENDOR	PO#	PRODUCT LINE
CONTIXO	2401242	DROP	DON'T NEED
CONTIXO	2401242	DROP	DON'T NEED
CONTIXO	2401242	DROP	DON'T NEED
CONTIXO	2401242	DROP	DON'T NEED
MATTEL	2401242	MISC	DON'T NEED
KIDGAL	2401242	MISC	DON'T NEED
CONTIXO	2401246	DROP	KEEP
WONFOLD	2401246	DROP	KEEP
STANS	2401253	DROP	KEEP
ROYALG	2401253	DROP	KEEP
STANS	2401253	MISC	KEEP

Sorry, I should clarify that I am looking for a TRUE/FALSE formula to find these

 

[Fluff]

As this is a significantly different question, it needs a new thread. Thanks

 

[JENWPS]

As this is a significantly different question, it needs a new thread. Thanks

Ok, thanks!