r/excel•
DisDataWang

Converting Monthly data to quarterly, while keeping columns as months?

solved

I'm not sure if this is appropriate for this forum, but thought I would try. I'm trying to get excel to read monthly data and add it up on a quarterly basis but not specifically on the same quarter, while still using monthly columns.

To illustrate, I have two sheets. The first is 'accounting', which has $/month:

​

accounting recognition

I am looking for a formula/function that will add up those monthly cash flows on a quarterly basis so it shows up similar to the screen shot below.

​

cash timing

The 240 is based on a simple sum formula, but I am hoping to make this sheet dynamic so that I don't have to necessarily always have the quarterly cash payments on month 3/6/9/12 …a client might make payments on months 2/5/8/11 or 1/4/7/10. Ideally it would like to the contract start date in photo one, and basically add up three months at a time.

What are some functions that I could utilize that might solve this? Thanks for looking- sorry if this is not allowed, still learning the ways of reddit!

Posted Oct 2, 2021, 9:49 PM UTC

211
Loading comments…