Average Based On Conditions

[varunwalla]

How can i find the average based on certain conditions i.e if loss is present in b column it should not be calculated in average and should be ignored in g column here g3 column. so the average of remo should be 8.835714286

Average.xlsx
	A	B	C	D	E	F	G
1	Name	Type	Qty	price	total	Name	Average
2	Bob	profit	5	198.00	990	Bob	198
3	Remo	profit	30	8.85	265.5	Remo	9.027778
4	Remo	profit	5	8.75	43.75
5	Johna	profit	10	16.35	163.5
6	Remo	loss	33	9.15	301.95
7	Remo	loss	22	9.15	201.3
8	Johna	profit	34	23.00	782
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=SUMIF($A:$A,$F2,$E:$E)/SUMIF($A:$A,$F2,$C:$C)
E2:E8	E2	=(C2*D2)

 

[AhoyNC]

Try:
Also, it's best not to reference whole columns in your formulas. If data will only go to say row 5000 then use something like A2:A7000.

Book1
	A	B	C	D	E	F	G
1	Name	Type	Qty	price	total	Name	Average
2	Bob	profit	5	198	990	Bob	198
3	Remo	profit	30	8.85	265.5	Remo	8.835714286
4	Remo	profit	5	8.75	43.75
5	Johna	profit	10	16.35	163.5
6	Remo	loss	33	9.15	301.95
7	Remo	loss	22	9.15	201.3
8	Johna	profit	34	23	782
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=SUMIFS(E:E,A:A,$F2,B:B,"<>"&"loss")/SUMIFS(C:C,A:A,$F2,B:B,"<>"&"loss")
E2:E8	E2	=(C2*D2)

 

[varunwalla]

works perfectly

 

[varunwalla]

is it possible to return average zero if the profit and loss are same i.e remo profit 35 & loss 35 so the average return zero in g3

 

[AhoyNC]

I'm probably missing something here, but I don't see where the profit and loss for Remo is the same.

 

[varunwalla]

i mean if the qty becomes equal something like this remo profit 35 and loss 35