Averageifs using multiple criteria from multiple columns & getting a #Div/0 error

[tasub]

I'm at a loss and could use some advice. I'm trying to get an average based on criteria from 3 different columns. Also only calculating >0 Here is the formula I use. =AVERAGEIFS('Manual Data'!BA3:BA70,'Manual Data'!BA3:BA70,">0",'Manual Data'!G3:G70,"SALES",'Manual Data'!Z3:Z70,"Synergy Plan",'Manual Data'!Z3:Z70,"Standalone Plan")

It works taking out the last two criteria but returns the #div/0 error if I add them.

I need the data to give me averages by function. Only using Synergy Plan and Standalone Plan data in BA for the calculation.

 

[Sufiyan97]

Can you post some rows of your data using xl2bb?

 

[awoohaw]

Until we see some of your data, try to use this as an example. AVERAGEIFS works even if there are blank or text values in the measured column. So, one thing I'm thinking is that your lookup has no records or you have a mispelling.

Mr Excel Questions 3.xlsm
	A	B	C	D	E	F
1	Function	Position	Time To Define
2	Marketing	A	14		Function
3	Recruiting	A	10		Marketing	14
4	Helping	A	10
5	Cooking	A	15
6	Marketing	B
7	Recruiting	B	5
8	Helping	B	11
9	Cooking	B	15
10	Marketing	C	text
11	Recruiting	C	5
12	Helping	C	8
13	Cooking	C	9
AVERAGE IFS

Cell Formulas
Range	Formula
F3	F3	=AVERAGEIFS($C$2:$C$13,A2:A13,E3)

 

[awoohaw]

Until we see some of your data, try to use this as an example. AVERAGEIFS works even if there are blank or text values in the measured column. So, one thing I'm thinking is that your lookup has no records or you have a mispelling.

Mr Excel Questions 3.xlsm
	A	B	C	D	E	F
1	Function	Position	Time To Define
2	Marketing	A	14		Function
3	Recruiting	A	10		Marketing	14
4	Helping	A	10
5	Cooking	A	15
6	Marketing	B
7	Recruiting	B	5
8	Helping	B	11
9	Cooking	B	15
10	Marketing	C	text
11	Recruiting	C	5
12	Helping	C	8
13	Cooking	C	9
AVERAGE IFS

Cell Formulas
Range	Formula
F3	F3	=AVERAGEIFS($C$2:$C$13,A2:A13,E3)

Mr Excel Questions 3.xlsm
	E	F
1
2	Function
3	Mareting	#DIV/0!
4
5
AVERAGE IFS

Cell Formulas
Range	Formula
F3	F3	=AVERAGEIFS($C$2:$C$13,A2:A13,E3)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=AVERAGE(FILTER('Manual Data'!BA3:BA70,('Manual Data'!BA3:BA70>0)*('Manual Data'!G3:G70="SALES")*(('Manual Data'!Z3:Z70="Synergy Plan")+('Manual Data'!Z3:Z70="Standalone Plan"))))

 

[tasub]

I'm at a loss and could use some advice. I'm trying to get an average based on criteria from 3 different columns. Also only calculating >0 Here is the formula I use. =AVERAGEIFS('Manual Data'!BA3:BA70,'Manual Data'!BA3:BA70,">0",'Manual Data'!G3:G70,"SALES",'Manual Data'!Z3:Z70,"Synergy Plan",'Manual Data'!Z3:Z70,"Standalone Plan")

It works taking out the last two criteria but returns the #div/0 error if I add them.

I need the data to give me averages by function. Only using Synergy Plan and Standalone Plan data in BA for the calculation.

View attachment 85036

Hi & welcome to MrExcel.
How about

=AVERAGE(FILTER('Manual Data'!BA3:BA70,('Manual Data'!BA3:BA70>0)*('Manual Data'!G3:G70="SALES")*(('Manual Data'!Z3:Z70="Synergy Plan")+('Manual Data'!Z3:Z70="Standalone Plan"))))

This WORKED!!! Thank you so much!!

 

[Fluff]

You're welcome & thanks for the feedback.