3 IF statements - can't work it out !

[TURK]

Hi all,

I have one IF statement in column W that eliminates IAIN in column I, as he is not elegable for bonus's, the formuls is;<B>=IF(I2:I33="IAIN",0,F2*0.02)</B> this works fine, but I want to add another IF statement to not count bonus's for SHOP that is also in column I.

To complicate things further, the bonus is only payable if COM exists in column B. How do I go about this ?

01.January.2009.Analysis_TEST.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	JobNo	Status	CNXCode	ActVisit	Service	Rate	Type	CustomerName	Installer	1	2	3	4	5	6	7	RefNo	InvNo	Paid	OrderNo	Payment	Outstanding	2%Bonus
2	53804221	COM			SP9	56.62	S	SANDALL	OWAIN	1									56.62			0.00	1.13
3	53611220	COM			IXX	100.80	I	LIDDLE	AARON			1							100.80			0.00	2.02
4	53535488	CAN				0.00	I	MOHUN	AARON			X										0.00	0.00
5	53572301	COM			SHV	55.58	S	BEWHAY	OWAIN	1									55.58			0.00	1.11
6	53553803	COM			IXX	100.80	I	CHESHIRE	AARON			1							100.80			0.00	2.02
7	53529816	COM			SHV	55.58	S	BOUGOURD	OWAIN	1									55.58			0.00	1.11
8	53802351	COM			SDS	56.45	S	MCCARTER	OWAIN	1									56.45			0.00	1.13
9	53804288	COM			SDW	56.45	S	HOWARD	OWAIN	1									56.45			0.00	1.13
10	53523857	COM			IZ1	100.80	I	EAST	AARON			1							100.80			0.00	2.02
11	53796229	COM			SDS	56.45	S	PALOMBA	OWAIN	1									56.45			0.00	1.13
12	53528899	CAN				0.00	I	QUENAULT	OFFICE						X							0.00	0.00
13	53519749	COM			IZ1	100.80	I	VIBERT	SHOP					1					100.80			0.00	2.02
14	53792175	COM			SDW	56.45	S	NOEL	OWAIN	1									56.45			0.00	1.13
15	53796359	COM			SPA	56.66	S	ROBERTS	OWAIN	1									56.66			0.00	1.13
16	53797685	COM			SPA	56.66	S	JOHN	OWAIN	1									56.66			0.00	1.13
17	53367377	COM			IZ1	100.80	I	BECK	IAIN							1			100.80			0.00	0.00
18	53528252	CAN				0.00	I	BUCKLEY	OFFICE						X							0.00	0.00
19	53640780	COM			SHD	51.57	S	JOHNSON	SHOP					1					51.57			0.00	1.03
Fri.2

 

[Weaver]

Looking at your formula, the multi cell range is not necessary, as I think it only ever looks at the first cell, so

=IF(I2:I33="IAIN",0,F2*0.02)

shoud be

=IF(I2="IAIN",0,F2*0.02)

If you want to exclude "shop" from this bonus calc, you could use:

=IF(OR(I2="IAIN",I2="SHOP),0,F2*0.02)

to add the last condition, try:

=IF(OR(I2="IAIN",I2="SHOP,B2<>"COM"),0,F2*0.02)

 

[Stormseed]

Hi,

Check out the table below:

Assuming that you house the values "IAIN" OR "SHOP" in cell K1 and house the value "COM" in cell L1, as per the data you have shown above, the formula is in cell W2:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1											shop	com
2		com				1000			shop														20.00
3
Sheet1

Is this what you want ?