# 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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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

1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

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