Multiple Ifs based on Occurances

[Squad]

What im trying to do is best described below in the solve section below. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o><o></o>
Solve:<o></o><o></o>
Show “1” where multiple orders occur (with the same order number) AND where multiple product types exist<o></o>
-AND-<o></o>
Show “0” where multiple orders occur (with the same order number) AND where single product types exist<o></o>
-AND-<o></o>
Show “0” where single orders occur<o></o><o></o><o></o>
Data has: Order Numbers & Product Type

Excel Workbook
	B	C	D
3	Order Numbers	Product Type	??Solve??
4	83934	Air Max	0
5	83934	Air Max	0
6	83934	Air Max	0
7	83934	Air Max	0
8	83934	Air Max	0
9	20493	Legend	1
10	20493	Legend	1
11	20493	Classic	1
12	47623	Air Max	1
13	47623	Legend	1
14	47623	Classic	0
15	47623	Classic	0
16	33343	Legend	0
17	71671	Classic	0
18	71671	Classic	0
19	68500	Air Max	1
20	68500	Classic	1
Sheet2

Excel 2007



<o></o>

 

[number kruncher]

You could use an IF statement to test for a COUNT in each Column, eg..

IF(and(countif(b:b,b4)>1,countif(c:c,c4)>1),1,0)

HTH

 

[WhatTheXL]

I'm a little Stumped by your Data
20493 Legend are both 1, however
47623 Classic are both 0.

if 20493 are the correct lines.
Try:

=--(SUMPRODUCT(--($C$4:$C$20=C4),--($D$4:$D$20=D4))<>SUMPRODUCT(--($C$4:$C$20=C4)))

I do not have 2007 but Countifs should work better in 2007
From what I have read about Sumifs

 

[Squad]

I'm a little Stumped by your Data
20493 Legend are both 1

Sorry, let me try and explain.

The data i have will be found in columns B and C of my previous post. I am looking for a formula to give me the results found in column D labeled ?? Solved??.

The data within can have single and multiple order numbers. The reason multiple order numbers exist is:

a) Because there are multiple products within the same order which would be illustrated line by line)
-or-
b) Due to a completion date (some companies like for us to send them a product when they are ready for it). So even though they might have multiple orders of the same product, we illustrate the data line by line.

The formula in column d should resemble the following:

Show “1” where multiple orders occur (with the same order number) AND where multiple product types exist<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

-AND-<o></o>
Show “0” where multiple orders occur (with the same order number) AND where single product types exist<o></o>
-AND-<o></o>
Show “0” where single orders occur

<o>What this will do is identify multiple order numbers where there is a mix of products. The "1" will identify this while the "0" will show orders that shouldnt be considered within an analysis.</o>

 

[WhatTheXL]

Show “1” where multiple orders occur (with the same order number) AND where multiple product types exist<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

47623 Classic both lines should be 1 per the above line.

In which case this will work (adjusted to columns B & C)

=--(SUMPRODUCT(--($B$4:$B$20=B4),--($C$4:$C$20=C4))<>SUMPRODUCT(--($B$4:$B$20=B4)))

 

[Squad]

47623 Classic both lines should be 1 per the above line.

In which case this will work (adjusted to columns B & C)

=--(SUMPRODUCT(--($B$4:$B$20=B4),--($C$4:$C$20=C4))<>SUMPRODUCT(--($B$4:$B$20=B4)))

That is correct. The data should look like this

Excel Workbook
	B	C	D
3	Order Numbers	Product Type	??Solve??
4	83934	Air Max	0
5	83934	Air Max	0
6	83934	Air Max	0
7	83934	Air Max	0
8	83934	Air Max	0
9	20493	Legend	1
10	20493	Legend	1
11	20493	Classic	1
12	47623	Air Max	1
13	47623	Legend	1
14	47623	Classic	1
15	47623	Classic	1
16	33343	Legend	0
17	71671	Classic	0
18	71671	Classic	0
19	68500	Air Max	1
20	68500	Classic	1
Sheet2

Excel 2007


However, your formula returns all zeros

 

[Squad]

My data was messed up. The formula works perfect. Thanks