AVERAGEIFS with OR condition

[creative999]

Trying to work out the average based on a OR condition. For example:

IF A=Mens
AND
B=Retail or Warehouse
then calculate average $ from C

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E
1	Mens	retail	1
2	Women	retail	2		2.5
3	Women	warehouse	3
4	Mens	warehouse	4
5	Mens	other	5
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(AVERAGEIFS(C1:C5,A1:A5,"mens",B1:B5,{"retail","warehouse"}))

 

[creative999]

Try:

Book1
	A	B	C	D	E
1	Mens	retail	1
2	Women	retail	2		2.5
3	Women	warehouse	3
4	Mens	warehouse	4
5	Mens	other	5
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(AVERAGEIFS(C1:C5,A1:A5,"mens",B1:B5,{"retail","warehouse"}))

Thank you, but this doesnt work (DIV error) if there's no MENS RETAIL or MENS WAREHOUSE

 

[Fluff]

How about

=AVERAGE(FILTER(C2:C100,(A2:A100="mens")*((B2:B100="retail")+(B2:B100="warehouse"))))

 

[creative999]

How about

=AVERAGE(FILTER(C2:C100,(A2:A100="mens")*((B2:B100="retail")+(B2:B100="warehouse"))))

Gives a VALUE error

 

[Fluff]

Do you have any #value errors in cols A or B

 

[AhoyNC]

You could wrap my formula in the IFERROR function (Fluff's too). This will return a 0, but you can change it to blank or anything slse you want to return for no data.
Change cells ranges to match your data.
=IFERROR(AVERAGE(AVERAGEIFS(C1:C5,A1:A5,"mens",B1:B5,{"retail","warehouse"})),0)

 

[Fluff]

Averaging averages will often give the wrong result.

 

[AhoyNC]

I would go with Fluff's formula, mine seems to pick up some bad data.

 

[creative999]

Do you have any #value errors in cols A or B

Ah yes. Fixed some data and now working. Thank you so much