Averageifs Formula with or criteria

[NorthbyNorthwest]

Hello, everyone. I am having trouble constructing a formula in which I need to average with multiple conditions: both "or" conditions and "and" conditions. I started out with AVERAGEIFS and learned AVERAGEIFS is not compatible with "or." I then tried dividing two SUMPRODUCT results. I have not been able to produce an answer. I keep getting some type of error: problem with formula pop-up, DIV/0, or SPILL. I've been at this almost three hours. Hoping someone can help. I'm trying to get the average of a list of "accuracy scores" if...

• audit month = 5/1/2021
• review type <> "Feedback" or "OJT Feedback (Note: It seemed easier to have formula consider what it did not equal as the list of review types it could equal are five)
• audit group = Group A or Group B

Any assistance would be greatly appreciated.

 

[Toadstool]

Hi NorthbyNorthWest,

Does this do what you want?

NorthbyNorthWest.xlsx
	A	B	C	D	E	F
1	Audit Month	Review Type	Audit Group	Score		Audit Month
2	5/1/2021	Feedback	A	100		5/1/2021
3	5/1/2021	OJT Feedback	B	200
4	5/1/2021	Feedback	A	300		Result
5	5/1/2021	Feedback	B	400		350
6	5/1/2021	OJT Feedback	C	500
7	5/1/2021	Not either	A	250
8	5/1/2021	Feedback	B	350
9	2/2/2021	OJT Feedback	A	450
10	5/1/2021	Not either	D	550
11	5/1/2021	Feedback	D	650
12	5/1/2021	OJT Feedback	A	750
13	5/1/2021	Not either	C	850
Sheet1

Cell Formulas
Range	Formula
F5	F5	=SUMPRODUCT(($D$2:$D$13)*(($A$2:$A$13=$F$2)*(($B$2:$B$13="Feedback")+($B$2:$B$13="OJT Feedback"))*(($C$2:$C$13="A")+($C$2:$C$13="B"))))/SUMPRODUCT((($A$2:$A$13=$F$2)*(($B$2:$B$13="Feedback")+($B$2:$B$13="OJT Feedback"))*(($C$2:$C$13="A")+($C$2:$C$13="B"))))

 

[NorthbyNorthwest]

Hi NorthbyNorthWest,

Does this do what you want?

NorthbyNorthWest.xlsx
	A	B	C	D	E	F
1	Audit Month	Review Type	Audit Group	Score		Audit Month
2	5/1/2021	Feedback	A	100		5/1/2021
3	5/1/2021	OJT Feedback	B	200
4	5/1/2021	Feedback	A	300		Result
5	5/1/2021	Feedback	B	400		350
6	5/1/2021	OJT Feedback	C	500
7	5/1/2021	Not either	A	250
8	5/1/2021	Feedback	B	350
9	2/2/2021	OJT Feedback	A	450
10	5/1/2021	Not either	D	550
11	5/1/2021	Feedback	D	650
12	5/1/2021	OJT Feedback	A	750
13	5/1/2021	Not either	C	850
Sheet1

Cell Formulas
Range	Formula
F5	F5	=SUMPRODUCT(($D$2:$D$13)*(($A$2:$A$13=$F$2)*(($B$2:$B$13="Feedback")+($B$2:$B$13="OJT Feedback"))*(($C$2:$C$13="A")+($C$2:$C$13="B"))))/SUMPRODUCT((($A$2:$A$13=$F$2)*(($B$2:$B$13="Feedback")+($B$2:$B$13="OJT Feedback"))*(($C$2:$C$13="A")+($C$2:$C$13="B"))))

Yes. I was all around the solution. I am oftentimes thrown by the parenthesis when dealing with long formulas with multiple conditions. Add nested conditions to the formula, and... Thanks so much for rescuing me. I cannot express how great this forum has been to me over the years. Have a wonderful weekend Toadstool.

 

[Toadstool]

Yes. I was all around the solution. I am oftentimes thrown by the parenthesis when dealing with long formulas with multiple conditions. Add nested conditions to the formula, and... Thanks so much for rescuing me. I cannot express how great this forum has been to me over the years. Have a wonderful weekend Toadstool.

You're welcome, and thanks for the feedback.

 

[smozgur]

Thanks so much for rescuing me.

I changed the solution post with the obvious answer.

@NorthbyNorthwest - in the future, please mark the post as the solution that answered your question instead of your feedback post. This way, it will be more helpful for future readers.