Revenue allocation using combined/Nested IF statements

Hi,

I have been asked to go through some information which has over 200 000 lines, what I need to do is as follows:

where order number is unique, revenue source = "Unique"
where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package"

I need all of the above in formula line which will be inserted in the revenue source column

I have attached the file with a sample of the information I am using.

Thanks in advance,

No attachment... try again.

Try:

=IF(COUNTIF($A$2:$A$434,A2)=1,"Unique",IF(SUMPRODUCT(($A$2:$A$434=A2)*($G$2:$G$434=G2))>1,"Cross Selling","Division Packaging"))

copied down.

Adjust ranges to suit...

Note: Whole column references not allowed if not using XL2007

Edited: Referenced wrong column for Divisions conditional check.

Originally Posted by Irfan123

Hi,

Thanks for the quick response.

I am using excel 2007.

That formula is nearly there, only problem is:

where the unique reference number appears more than once but in the same division, it should be "Divisional Packaging", at present this is coming up as "cross selling"

Many thanks,

This seems to be opposite what you said in your original post....

Anyways, try:

=IF(COUNTIF($A$2:$A$434,A2)=1,"Unique",IF(SUMPRODUCT(($A$2:$A$434=A2)*($G$2:$G$434=G2))>1,"Division Packaging","Cross Selling"))

So does that mean my last formula solved the problem...or is there still something not right?

This is my results...

can you point out which are wrong.