IF statement

[jwgreen1986]

hi, was wondering if someone could help. this is probably simple but has my head a bit messed.

basically i want to do a formula where if the values of two cells dont match something then sum 3 cells to give a total.

in one column i have account. and in the other i have department.
i want the formula to look in this and when the account is not equal to gross profit AND department is not equal to USED VEHICLES then add up 3 different cells to give a total.

the important part is the AND. i want other accounts of used vehicles to be calculated (units and F&I) but i do not want it to add any where the department and account equals gross profit and used vehicles.


similarly i want other departments of gross profits to be calculated (new vehicles etc)

so basically i want 3 cells adding up if the account cell AND department cell do not equal gross profit and used vehicles. it has to be both and not just one or the other.

 

[Fluff]

Hi & welcome to MrExcel.
Something like
=IF(AND(A1<>"Gross profit",B1<>"Used vehicles"),SUM(C1:E1),"")

 

[FormR]

Hi, I've read it differently - here is my take.

i do not want it to add any where the department and account equals gross profit and used vehicles.

Excel 2013/2016
	A	B	C	D	E	F
1	account	department	different cell 1	different cell 2	different cell 3	formula
2	gross profit	used vehicles	95	98	31	0
3	gross profit	used vehicles	71	27	12	0
4	gross profit	other	7	20	36	63
5	other	a	74	61	66	201
6	abc	a	21	9	77	107
7	hello	used vehicles	43	12	47	102
8	gross profit	aa	12	13	25	50
9	gross profit	used vehicles	40	90	76	0
10	gross profit	klsa	32	31	34	97
Sheet1

Cell Formulas
Range	Formula
F2	=IF(AND(A2="gross profit",B2="used vehicles"),0,SUM(C2:E2))

 

[jwgreen1986]

brilliant mate, that is exactly what i wanted. thanks

 

[jwgreen1986]

right i need to extend this further.
i have managed to calculate the 3 monthly totals based on the criteria above. i have then marked this against a target total and this gives how much of the target the person has hit in %.
what i need to do now is give the person a bonus based on this percentage. so the formula i need is like this

if account ="Gross Profit on F&I" OR Account = "Vehicle Units" AND Department = "Used Vehicles", IF difference <85,0, if difference =>85 BUT <95, but the value from a different cell on a different tab, if difference =>95 but <100, then but value from a difference cell, if difference =>100 but <110, then put value from different cell, if difference >=110 but < 120, then put value from a difference cell, if difference >120 then put value from different cell. i need to separate the Gross profit on F&I and the vehicle units as there are different rates for these. so basically i will need it to first look at the account column first and if this is Gross Profit on F&I and the department is Used Vehicles then do the formula, if account equals vehicle units and department is used vehicles then do the formula, if the account is anything other than one of these two then simply bring back 0.

 

[FormR]

Hi, see if you can adapt something like this to your set-up.

Excel 2013/2016
	A	B	C	D	E	F
1	Account	department	difference	results		different cell 1
2	Gross Profit on F&I	Used Vehicles	84	0		different cell 2
3	Vehicle Units	Used Vehicles	85	different cell 1		different cell 3
4	Gross Profit on F&I	other	500	0		different cell 4
5	Vehicle Units	Used Vehicles	95	different cell 2		different cell 5
6	other	Used Vehicles	100	0
7	other	other	151	0
8	Vehicle Units	Used Vehicles	100	different cell 3
9	Gross Profit on F&I	Used Vehicles	110	different cell 4
10	Vehicle Units	Used Vehicles	120	different cell 5
11	Vehicle Units	Used Vehicles	9999	different cell 5
Sheet1

Cell Formulas
Range	Formula
D2	=IF(AND(OR(A2={"Gross profit on f&I","vehicle units"}),B2="Used vehicles"),CHOOSE(MATCH(C2,{0,85,95,100,110,120}),0,$F$1,$F$2,$F$3,$F$4,$F$5),0)