Convert monthly payments into yearly total fields

I am trying to build a workflow that will take an excel file with multiple column fields including monthly payments that go out 20 years.

I would like to group this data by more than 1 column field and then summarize by year. Is there a formula that will look at the year in a date and then create and add new fields that add up the total payments by year for each grouping?

Help

Accepted answers

marcusblackhill

@jmlabelle65

Ok, I think that attached example will help you

EXAMPLE.yxmd

marcusblackhill

Try this modification

EXAMPLE.yxmd

All comments

marcusblackhill

Hi @jmlabelle65 !

Will be better if you can share a example data with us but I understand being some like this

Record	01/2019	02/2019
1	10	30

That way, you maybe can use transpose to to make only one column with the monthly data and create a column to get the year, maybe by DateTimeYear if you data have the datatype like date or using right formula if is string. With that new column you can use summarize tool to group by year and sum the total in values column.

Hope that help!

Qiu

@jmlabelle65
I believe Transpose Tool is what we need here.

Just forged some data to demostrate.

0106-jmlabelle65.yxmd

jmlabelle65

Attached is a sample of the data, I would like to group by the 1st 2 fields and then sum the total payments for each by year

Sample.xlsx

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335