AverageIfs with multiple criteria and multiple ranges

[ExecuChic]

Hello all,

Thank you in advance for any assistance!!!

I've created a scorecard that tracks performance for one of our patient follow-up teams. I am attempting to track patient satisfaction over time.

Successfully generated a result with an AverageIfs function with two criteria =AVERAGEIFS(DataExport!U:U,DataExport!$D:$D,"Ongoing Behv Hlth CM",DataExport!$AE:$AE,"RBHA Adult")

Running into the following challenges:
1. Unable to generate result for single range using 3 criteria (unsure why because my understanding of the function is that it should work fine). Receive a #Div/0 error. =AVERAGEIFS(DataExport!$U:$U, DataExport!$D:$D, "Ongoing Behv Hlth CM",DataExport!$E:$E,"Therapy Only",DataExport!AE:AE,"RBHA Adult")

2. Unable to generate result using 2 or more criteria over 2 ranges. Would like to average the same question (Q4) from two output sources.

Below are screen shots, highlighted the columns pertinent to the calculation.

Visit_Type	Behavioral_Status	Q4_Outreach_Daily_Activities	Q4_Ongoing_Daily_Activities	Q5_Ongoing_Health	Q6_Ongoing_Quality_of_Life	Program_Desc	Client_ID
Ongoing Behv Hlth CM	Meds/Therapy					RBHA Adult
Ongoing Behv Hlth CM	CM Only		8	8	9	RBHA Adult
Ongoing Behv Hlth CM	Therapy Only					SWNetwork Child
Ongoing Behv Hlth CM	Therapy Only		10	10	10	SWNetwork Child
Ongoing Behv Hlth CM	Meds/Therapy		5	5	10	SWNetwork Child
Ongoing Behv Hlth CM	Meds/Therapy					SWNetwork Child
Ongoing Behv Hlth CM	Meds Only		8	10	8	SWNetwork Child
Ongoing Behv Hlth CM	Meds/Therapy					SWNetwork Child

<colgroup><col width="140" style="width: 105pt; mso-width-source: userset; mso-width-alt: 5120;"> <col width="64" style="width: 48pt;" span="3"> <col width="64" style="width: 48pt;" span="2"> <col width="64" style="width: 48pt;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

Ongoing CM Average Scores
Totals	8.00	17%	8.00	28%	9.00	44%
	Meds/Therapy	5.33	-18%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
	Therapy Only	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Meds Only	8.00	7%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
	Other	N/A	N/A	N/A	N/A	N/A	N/A
		Survey Q4	% of Change Since Initial	Survey Q5	% of Change Since Initial	Survey Q6	% of Change Since Initial

<tbody>
</tbody>

 

[AlanY]

your formula work ok, the error was due to the fact that none met all the 3 criteria.
try amend your data in RED for testing.

Hello all,

Running into the following challenges:
1. Unable to generate result for single range using 3 criteria (unsure why because my understanding of the function is that it should work fine). Receive a #Div/0 error. =AVERAGEIFS(DataExport!$U:$U, DataExport!$D:$D, "Ongoing Behv Hlth CM",DataExport!$E:$E,"Therapy Only",DataExport!AE:AE,"RBHA Adult")

Below are screen shots, highlighted the columns pertinent to the calculation.

Visit_Type	Behavioral_Status	Q4_Outreach_Daily_Activities	Q4_Ongoing_Daily_Activities	Q5_Ongoing_Health	Q6_Ongoing_Quality_of_Life	Program_Desc	Client_ID
Ongoing Behv Hlth CM	Meds/Therapy					RBHA Adult
Ongoing Behv Hlth CM	CM Only		8	8	9	RBHA Adult
Ongoing Behv Hlth CM	Therapy Only		8			RBHA Adult
Ongoing Behv Hlth CM	Therapy Only		10	10	10	SWNetwork Child
Ongoing Behv Hlth CM	Meds/Therapy		5	5	10	SWNetwork Child
Ongoing Behv Hlth CM	Meds/Therapy					SWNetwork Child
Ongoing Behv Hlth CM	Meds Only		8	10	8	SWNetwork Child
Ongoing Behv Hlth CM	Meds/Therapy					SWNetwork Child

<tbody>
</tbody>

Ongoing CM Average Scores
Totals	8.00	17%	8.00	28%	9.00	44%
	Meds/Therapy	5.33	-18%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
	Therapy Only	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Meds Only	8.00	7%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
	Other	N/A	N/A	N/A	N/A	N/A	N/A
		Survey Q4	% of Change Since Initial	Survey Q5	% of Change Since Initial	Survey Q6	% of Change Since Initial

<tbody>
</tbody>