AverageIfs with multiple criteria and multiple ranges

E

ExecuChic

New Member
Joined
Jun 6, 2016
Messages
1
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.

[TABLE="width: 441"]
<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>[TR]
[TD="class: xl68, width: 140, bgcolor: yellow"]Visit_Type[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]Behavioral_Status[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]Q4_Outreach_Daily_Activities[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]Q4_Ongoing_Daily_Activities[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Q5_Ongoing_Health[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Q6_Ongoing_Quality_of_Life[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]Program_Desc[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Client_ID[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Meds/Therapy[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow"]RBHA Adult[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]CM Only[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]8[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl68, bgcolor: yellow"]RBHA Adult[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Therapy Only[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Therapy Only[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]10[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Meds/Therapy[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]5[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Meds/Therapy[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Meds Only[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]8[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Meds/Therapy[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 473"]
<tbody>[TR]
[TD="class: xl78, width: 631, bgcolor: #7F7F7F, colspan: 8"]Ongoing CM Average Scores
[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent, colspan: 2"]Totals
[/TD]
[TD="class: xl87, bgcolor: #00B050, align: right"]8.00
[/TD]
[TD="class: xl72, bgcolor: #7F7F7F"]17%
[/TD]
[TD="class: xl87, bgcolor: #00B050, align: right"]8.00
[/TD]
[TD="class: xl72, bgcolor: #7F7F7F"]28%
[/TD]
[TD="class: xl87, bgcolor: #00B050, align: right"]9.00
[/TD]
[TD="class: xl72, bgcolor: #7F7F7F"]44%
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]Meds/Therapy
[/TD]
[TD="class: xl83, bgcolor: yellow, align: right"]5.33
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]-18%
[/TD]
[TD="class: xl86, bgcolor: yellow, align: center"]#DIV/0!
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0!
[/TD]
[TD="class: xl86, bgcolor: yellow, align: center"]#DIV/0!
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Therapy Only
[/TD]
[TD="class: xl84, bgcolor: yellow, align: center"]#DIV/0!
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0!
[/TD]
[TD="class: xl84, bgcolor: yellow, align: center"]#DIV/0!
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0!
[/TD]
[TD="class: xl84, bgcolor: yellow, align: center"]#DIV/0!
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent, colspan: 2"]Meds Only
[/TD]
[TD="class: xl85, bgcolor: yellow, align: right"]8.00
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]7%
[/TD]
[TD="class: xl85, bgcolor: yellow, align: center"]#DIV/0!
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0!
[/TD]
[TD="class: xl85, bgcolor: yellow, align: center"]#DIV/0!
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]Other
[/TD]
[TD="class: xl77, bgcolor: #7F7F7F"]N/A
[/TD]
[TD="class: xl76, bgcolor: #7F7F7F"]N/A
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]N/A
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]N/A
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]N/A
[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]N/A
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]Survey Q4
[/TD]
[TD="class: xl71, width: 76, bgcolor: transparent"]% of Change Since Initial
[/TD]
[TD="class: xl70, bgcolor: transparent"]Survey Q5
[/TD]
[TD="class: xl71, width: 76, bgcolor: transparent"]% of Change Since Initial
[/TD]
[TD="class: xl70, bgcolor: transparent"]Survey Q6
[/TD]
[TD="class: xl71, width: 76, bgcolor: transparent"]% of Change Since Initial
[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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.

ExecuChic said:
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.

[TABLE="width: 441"]
<tbody>[TR]
[TD="class: xl68, width: 140, bgcolor: yellow"]Visit_Type[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]Behavioral_Status[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]Q4_Outreach_Daily_Activities[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]Q4_Ongoing_Daily_Activities[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Q5_Ongoing_Health[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Q6_Ongoing_Quality_of_Life[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]Program_Desc[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Client_ID[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Meds/Therapy[/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: yellow"]RBHA Adult[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]CM Only[/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]8[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl68, bgcolor: yellow"]RBHA Adult[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Therapy Only[/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]8[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: yellow"]RBHA Adult[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Therapy Only[/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]10[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Meds/Therapy[/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]5[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Meds/Therapy[/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Meds Only[/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]8[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Ongoing Behv Hlth CM[/TD]
[TD="class: xl68, bgcolor: yellow"]Meds/Therapy[/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: yellow"]SWNetwork Child[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 473"]
<tbody>[TR]
[TD="class: xl78, width: 631, bgcolor: #7F7F7F, colspan: 8"]Ongoing CM Average Scores[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent, colspan: 2"]Totals[/TD]
[TD="class: xl87, bgcolor: #00B050, align: right"]8.00[/TD]
[TD="class: xl72, bgcolor: #7F7F7F"]17%[/TD]
[TD="class: xl87, bgcolor: #00B050, align: right"]8.00[/TD]
[TD="class: xl72, bgcolor: #7F7F7F"]28%[/TD]
[TD="class: xl87, bgcolor: #00B050, align: right"]9.00[/TD]
[TD="class: xl72, bgcolor: #7F7F7F"]44%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]Meds/Therapy[/TD]
[TD="class: xl83, bgcolor: yellow, align: right"]5.33[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]-18%[/TD]
[TD="class: xl86, bgcolor: yellow, align: center"]#DIV/0![/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0![/TD]
[TD="class: xl86, bgcolor: yellow, align: center"]#DIV/0![/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0![/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Therapy Only[/TD]
[TD="class: xl84, bgcolor: yellow, align: center"]#DIV/0![/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0![/TD]
[TD="class: xl84, bgcolor: yellow, align: center"]#DIV/0![/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0![/TD]
[TD="class: xl84, bgcolor: yellow, align: center"]#DIV/0![/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0![/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent, colspan: 2"]Meds Only[/TD]
[TD="class: xl85, bgcolor: yellow, align: right"]8.00[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]7%[/TD]
[TD="class: xl85, bgcolor: yellow, align: center"]#DIV/0![/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0![/TD]
[TD="class: xl85, bgcolor: yellow, align: center"]#DIV/0![/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]#DIV/0![/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]Other[/TD]
[TD="class: xl77, bgcolor: #7F7F7F"]N/A[/TD]
[TD="class: xl76, bgcolor: #7F7F7F"]N/A[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]N/A[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]N/A[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]N/A[/TD]
[TD="class: xl73, bgcolor: #7F7F7F"]N/A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]Survey Q4[/TD]
[TD="class: xl71, width: 76, bgcolor: transparent"]% of Change Since Initial[/TD]
[TD="class: xl70, bgcolor: transparent"]Survey Q5[/TD]
[TD="class: xl71, width: 76, bgcolor: transparent"]% of Change Since Initial[/TD]
[TD="class: xl70, bgcolor: transparent"]Survey Q6[/TD]
[TD="class: xl71, width: 76, bgcolor: transparent"]% of Change Since Initial[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
1K
arthurbr
arthurbr
L
  • Question Question
Averageifs with multiple criteria based on KPI target on monthly
Replies
0
Views
780
Lukma
L
DrMrsStark
  • Question Question
Formula to search range by two criteria or different two criteria, and if not found then search different range using same criteria(s)
Replies
5
Views
2K
jasonb75
J
G
  • Question Question
Standard deviation on multiple criteria not working
Replies
2
Views
2K
gwfami
G
P
  • Question Question
AVERAGEIFS with multiple criteria on one range
Replies
1
Views
8K
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top