Excel 2002: How to select an invoice entry ?

[Mr. Low]

Hi,

Let's consider the table below:

A B C
1 Invoice Line code Selection
2 2010030101 18 A
3 2010030101 2 A
4 2010030102 3 B
5 2010030102 3 B
6 2010030103 18 A
7 2010030103 6 A
8 2010030104 4 B
9 2010030104 6 B
10 2010030105 5 B
11 2010030105 2 B
12 2010030106 1 A
13 2010030106 1 A
14 2010030106 18 A
15 2010030106 2 A

May I know the formula to input at cell C2 in order to label each invoice
batch as "A" when one of its line codes is "18" ? Otherwise label it as "B".

Thanks

Low

 

[Jeanette]

=IF(B1=18,"A","B")

 

[Jeanette]

Sorry B2, not B1

=IF(B2=18,"A","B")

=IF(B1=18,"A","B")

 

[Mr. Low]

Hi Jeanette,

This only select the row with "18", not the the rest of the line with the
similar invoice number, eg, I need to select row 12 to row 15 as "A" when
onlt row 14 has the code "18". Any other solution ?

 

[Max]

Try this in C2, which will involve both the invoice number and line code:
=IF(SUMPRODUCT(($A$2:$A$15=A2)*($B$2:$B$15=18))>0,"A","B")
Copy down to return desired results. Success? hit YES below

 

[Mr. Low]

Hi Max,

Your formula works.

Thanks for your help.

Low

 

[Max]

You're welcome, Low. Glad to hear.