AverageIfs Macro

H

hellotown2345_

New Member
Joined
Aug 28, 2019
Messages
1
Hi,

I am trying to create averages with three criteria. See example below. I tried to create using the AverageIf function however when I added three criteria it wouldn't work. Please Note the formula did work with three criteria. I was told I need to use macros to make it work. Please respond with example if possible. Thanks

I need the average for example of days for Portland, San diego and Austin. I cannot do AverageIfs with all three of those critera

A B
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"></colgroup><tbody>[TR]
[TD]Days[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]San Diego[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]San Diego[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Boston[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Austin[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Austin[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Miami[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Austin[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Austin[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Welcome to the MrExcel board!

These two statements seem to me to directly contradict each other. Can you please clarify?

hellotown2345_ said:
... three criteria it wouldn't work. Please Note the formula did work with three criteria.
Click to expand...

Can you also provide the expected result(s) for the sample data that you posted above?
 
Upvote 0
Try:
The result in cell C2.
The other formulas are only for checking.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:154.93px;" /><col style="width:88.4px;" /><col style="width:86.5px;" /><col style="width:92.2px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Days</td><td >City</td><td >AverageIfs</td><td >Portland</td><td >San Diego</td><td >Austin</td><td >Testing</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">5</td><td >Portland</td><td style="text-align:right; ">4.243589744</td><td style="text-align:right; ">3.230769231</td><td style="text-align:right; ">2</td><td style="text-align:right; ">7.5</td><td style="text-align:right; ">4.24358974</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >San Diego</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">1</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">3</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">2</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">2</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">2</td><td >San Diego</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">9</td><td >Boston</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">3</td><td >Austin</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">3</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">5</td><td >Austin</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">3</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">5</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">4</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">2</td><td >Miami</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">4</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">14</td><td >Austin</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="text-align:right; ">8</td><td >Austin</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style="text-align:right; ">1</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td style="text-align:right; ">6</td><td >Portland</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=AVERAGE(AVERAGEIF(B2:B39,{"Portland","San Diego","Austin"},A2:A39))</td></tr><tr><td >D2</td><td >=AVERAGEIF(B2:B39,"Portland",A2:A39)</td></tr><tr><td >E2</td><td >=AVERAGEIF(B2:B39,"San Diego",A2:A39)</td></tr><tr><td >F2</td><td >=AVERAGEIF(B2:B39,"Austin",A2:A39)</td></tr><tr><td >G2</td><td >=AVERAGE(D2:F2)</td></tr></table></td></tr></table>
 
Upvote 0
@ Dante
You cannot average averages unless the number of items in each group is identical. :)


@ hellotown2345_
You still should not need a macro. If the list of locations is in the sheet then try the formula in C2, otherwise try C3

Excel Workbook
ABCDEF
1DaysCityAveragePortland
25Portland4San Diego
32San Diego4Austin
43Portland
51Portland
60
70
83Portland
92Portland
100
110
122Portland
132San Diego
149Boston
153Austin
163Portland
175Austin
180
193Portland
205Portland
214Portland
222Miami
234Portland
2414Austin
250
260
278Austin
280
290
300
310
320
331Portland
340
350
360
370
386Portland
390
Average
 
Last edited:
Upvote 0
Peter_SSs said:
@ Dante
You cannot average averages unless the number of items in each group is identical. :)
Click to expand...

Hi Peter, You're right.


This is a correct option.

=SUM(SUMIF(B2:B39,{"Portland";"San Diego";"Austin"},A2:A39))/SUM(COUNTIF(B2:B39,{"Portland";"San Diego";"Austin"}))

 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Formula to aggregate emails if values in other cells match certain text
Replies
3
Views
504
Marcelo Branco
M
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
742
Fluff
Fluff
K
  • Question Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
1K
arthurbr
arthurbr
R
  • Question Question
Sumifs with dynamic criteria range
Replies
4
Views
1K
Peter_SSs
Peter_SSs
gtd526
  • Solved
vba remove unwanted text
Replies
6
Views
1K
gtd526
gtd526

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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