IF statement help please
 

I have data in the following form and I need a formula to produce the
contents of the Formula result column.

Invoice Product Value Formula result
1 car 10 No bus on invoice
1 car 10 No bus on invoice
1 bike 5 No bus on invoice
1 boat 15 No bus on invoice
1 train 50 No bus on invoice
2 bike 5 No bus on invoice
2 bike 5 No bus on invoice
2 boat 15 No bus on invoice
2 train 50 No bus on invoice
2 train 50 No bus on invoice
2 car 10 No bus on invoice
3 car 10 Bus on invoice
3 bus 25 Bus on invoice
3 tractor 20 Bus on invoice

Your help would be very much appreciated.

IF statement help please
 

try something like this

=IF(COUNTIFS(A:A,A2,B:B,"bus")=0,"No Bus on Invoice","Bus on Invoice")
then copy formula down

A:A represents the column that the invoice numbers are in
A2 represents the invoice cell for the row you are in
B:B represents the column for your transportation mode

Hope this helps


"nebula" wrote:

I have data in the following form and I need a formula to produce the
contents of the Formula result column.

Invoice Product Value Formula result
1 car 10 No bus on invoice
1 car 10 No bus on invoice
1 bike 5 No bus on invoice
1 boat 15 No bus on invoice
1 train 50 No bus on invoice
2 bike 5 No bus on invoice
2 bike 5 No bus on invoice
2 boat 15 No bus on invoice
2 train 50 No bus on invoice
2 train 50 No bus on invoice
2 car 10 No bus on invoice
3 car 10 Bus on invoice
3 bus 25 Bus on invoice
3 tractor 20 Bus on invoice

Your help would be very much appreciated.

IF statement help please
 

Hi,
I assume that product is in column B,

=if(B1<"Bus", "No bus on invoice","Bus on invoice")

"nebula" wrote:

I have data in the following form and I need a formula to produce the
contents of the Formula result column.

Invoice Product Value Formula result
1 car 10 No bus on invoice
1 car 10 No bus on invoice
1 bike 5 No bus on invoice
1 boat 15 No bus on invoice
1 train 50 No bus on invoice
2 bike 5 No bus on invoice
2 bike 5 No bus on invoice
2 boat 15 No bus on invoice
2 train 50 No bus on invoice
2 train 50 No bus on invoice
2 car 10 No bus on invoice
3 car 10 Bus on invoice
3 bus 25 Bus on invoice
3 tractor 20 Bus on invoice

Your help would be very much appreciated.

IF statement help please
 

Thanks. This worked for me.

"pmartglass" wrote:

try something like this

=IF(COUNTIFS(A:A,A2,B:B,"bus")=0,"No Bus on Invoice","Bus on Invoice")
then copy formula down

A:A represents the column that the invoice numbers are in
A2 represents the invoice cell for the row you are in
B:B represents the column for your transportation mode

Hope this helps


"nebula" wrote:

I have data in the following form and I need a formula to produce the
contents of the Formula result column.

Invoice Product Value Formula result
1 car 10 No bus on invoice
1 car 10 No bus on invoice
1 bike 5 No bus on invoice
1 boat 15 No bus on invoice
1 train 50 No bus on invoice
2 bike 5 No bus on invoice
2 bike 5 No bus on invoice
2 boat 15 No bus on invoice
2 train 50 No bus on invoice
2 train 50 No bus on invoice
2 car 10 No bus on invoice
3 car 10 Bus on invoice
3 bus 25 Bus on invoice
3 tractor 20 Bus on invoice

Your help would be very much appreciated.

IF statement help please
 

Many thanks for your reply. Sorry, if I wasn't clear that I needed to
identify all the rows for each invoice that includes a "bus" row.


"Eduardo" wrote:

Hi,
I assume that product is in column B,

=if(B1<"Bus", "No bus on invoice","Bus on invoice")

"nebula" wrote:

I have data in the following form and I need a formula to produce the
contents of the Formula result column.

Invoice Product Value Formula result
1 car 10 No bus on invoice
1 car 10 No bus on invoice
1 bike 5 No bus on invoice
1 boat 15 No bus on invoice
1 train 50 No bus on invoice
2 bike 5 No bus on invoice
2 bike 5 No bus on invoice
2 boat 15 No bus on invoice
2 train 50 No bus on invoice
2 train 50 No bus on invoice
2 car 10 No bus on invoice
3 car 10 Bus on invoice
3 bus 25 Bus on invoice
3 tractor 20 Bus on invoice

Your help would be very much appreciated.