# averageifs?

#### tly0227

##### New Member
I'm currently going through a data analysis tech bootcamp and am struggling trying to figure out a formula. There's an easier way to get the result I want, but my advisor is encouraging me to figure it out via a formula.

Sheet 1) WTRT
 B: # of units C: Price per Unit D: Month # 2674.43 89.33 1 2576.13 61.95 1 2131.98 45.90 2 2772.06 80.86 5 2143.49 55.96 6

Sheet 2) EMA
Need a formula that first finds the total cost for each purchase (WTRT B1*C1) and then averages it for each month. The first month # reference is cell C\$14 on this sheet. I'm well aware that I could just make a new column and get the totals, and then do an averageifs from there and it'd be a lot easier...but that idea has been nixed.

Is it possible to do this? Can someone please point me in the right direction? I've been messing around with it for a while and am getting nowhere good.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

##### Well-known Member
Hi Tly0227,

Does this do what you want?

WTRT
Book1
BCD
1B: # of unitsC: Price per UnitD: Month #
22674.4389.331
32576.1361.951
42131.9845.92
52772.0680.865
62143.4955.966
WTRT

EMA
Book1
CD
13Month # RefAverage
141199249
15297857.88
163
174
185224148.8
196119949.7
EMA
Cell Formulas
RangeFormula
D14:D19D14=IFERROR(SUMPRODUCT((WTRT!\$B\$2:\$B\$6*WTRT!\$C\$2:\$C\$6)*(WTRT!\$D\$2:\$D\$6=C14))/COUNTIF(WTRT!\$D\$2:\$D\$6,C14),"")

#### tly0227

##### New Member
I get an error when I try to do that...not sure why. The answer on yours def is what it should be, but I'm not sure iferror is the right formula on my end. The actual sheets will have data for all 12 months...so there's never going to be an instance where there is no average. There's 1460 transaction rows on WTRT. On EMA, the month #s are columns (I don't think that would be effecting why it's not working on my end, but just for more info..)

EMA:
B14C14D14E14F14
Month #
1​
234
Ave. Water Market Balancing Price
Ave. Quantity
=AVERAGEIFS('WTRT'!\$B:\$B,'WTRT'!\$D:\$D,'EMA'!C\$14)​
=AVERAGEIFS('WTRT'!\$B:\$B,'WTRT'!\$D:\$D,'EMA'!D\$14)​
=AVERAGEIFS('WTRT'!\$B:\$B,'WTRT'!\$D:\$D,'EMA'!E\$14)​
=AVERAGEIFS('WTRT'!\$B:\$B,'WTRT'!\$D:\$D,'EMA'!F\$14)​

##### Well-known Member
This should better match your format data

TLY0227.xlsx
BCD
1B: # of unitsC: Price per UnitD: Month #
22674.4389.331
32576.1361.951
42131.9845.92
5123
6124
72772.0680.865
82143.4955.966
WTRT

TLY0227.xlsx
BCDEFGH
14Month #123456
15Ave. Water Market Balancing Price199249.042797857.8822224148.8119949.7
16Ave. Quantity2625.282131.98112772.062143.49
EMA
Cell Formulas
RangeFormula
C15:H15C15=SUMPRODUCT((WTRT!\$B\$2:\$B\$1400*WTRT!\$C\$2:\$C\$1400)*(WTRT!\$D\$2:\$D\$1400=C14))/COUNTIF(WTRT!\$D\$2:\$D\$1400,C14)
C16:H16C16=AVERAGEIFS(WTRT!\$B:\$B,WTRT!\$D:\$D,EMA!C\$14)

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,652
Messages
5,832,928
Members
430,175
Latest member
Sheenamarie

### 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.

### Which adblocker are you using?

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

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