# Change monthly data in quarterly data based on site

#### JeanM01

##### New Member
Hello everyone,

I have data that looks something like this
 ID MOS Payment Volume 1001A Nov-13 \$100 80 1001A Dec-13 \$125 70 1001A Jan-14 \$160 20 . . . . . . . . 1001A Apr-15 \$ - 0 1001B Jan-12 \$200 10 1001B Feb-12 \$200 15 1002A Jan-14 \$900 5

<tbody>
</tbody>

In new columns, I would like to output the quarterly sum or average of these numbers within a year. I can't use an offsetting formula because some sites have less data than others others. And not every month will have data.

Jean

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
This formula will return the sum of payments in 15Q3
=SUMIFS(C:C,B:B,">9/30/2015",B:B,"<1/1/2016")

This formula will return the average of payments in 15Q3
=AVERAGEIFS(C:C,B:B,">9/30/2015",B:B,"<1/1/2016")

This formula will return the sum of payments in 15Q3
=SUMIFS(C:C,B:B,">9/30/2015",B:B,"<1/1/2016")

This formula will return the average of payments in 15Q3
=AVERAGEIFS(C:C,B:B,">9/30/2015",B:B,"<1/1/2016")

This is certainly helpful. If I could put this all in one column that would be better but thank you!

How do you want it to look in a single column?
What row should it appear in?
Try this
="15Q3 Avg Payment: \$" & AVERAGEIFS(C:C,B:B,">9/30/2015",B:B,"<1/1/2016") & " Total Payment \$" & SUMIFS(C:C,B:B,">9/30/2015",B:B,"<1/1/2016") & " Total Volume: " & SUMIFS(D:D,B:B,">9/30/2015",B:B,"<1/1/2016")

Replies
10
Views
431
Replies
0
Views
115
Replies
21
Views
305
Replies
3
Views
329
Replies
2
Views
184

1,207,108
Messages
6,076,589
Members
446,215
Latest member
userds5593

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

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