# Calculate average for multiple ranges for multiple criteria

#### cesareit

##### New Member
Hello,

I would like to calculate average values from multiple different ranges with varying number of sizes for multiple criteria. I have:

A B C D E
1 Day Time Hour Temperature Average Temp.
2 15.02.13 09:02:15 10 20,1 20,3
3 15.02.13 09:02:45 10 20,5
4 15.02.13 10:01:15 11 21,5
5 15.02.13 10:02:15 11 21,7
6 15.02.13 11:02:15 12 22
7 15.02.13 11:02:15 12 22,5
8 16.02.13 09:02:15 10 20,1
9 16.02.13 09:02:45 10 20,5
10 16.02.13 10:01:15 11 21,5
11 16.02.13 10:02:15 11 21,7
12 16.02.13 11:02:15 12 22
14 16.02.13 11:02:15 12 22,5
15 16.02.13 13:02:45 14 23,5

I want to get average temperature for a same day (in column A) and same hour (in column C) automatically calculated and written in the first row (in column E) for the corresponding day and hour. Example: 20,1 and 20,5 for day 15.02.2013 hour 10 -> average 20,3 in E2. Can anyone please help me?

Thank you!

PS: I tried: =AVERAGEIFS(\$D\$2:\$D\$13,\$A\$2:\$A\$13,A2,\$C\$2:\$C\$13,C2) in Excel 2010 but
I get: "the formula you typed contains an error highlighting "\$D\$13,\$A\$2" ...

Besides this syntax problem, this formula must be placed in the corresponding cells in column E automatically, since there are thousands of rows in the original table..

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Replies
1
Views
170
Replies
9
Views
180
Replies
0
Views
166
Replies
4
Views
278
Replies
9
Views
177

Threads
1,195,600
Messages
6,010,652
Members
441,558
Latest member
lambierules

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

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