How to combine multiple columns from a table

[mmetev]

Hello everybody,

My issue is the following: I have a table where I have multiple columns that have date and values but represent different things. Here is an example for my headers:

Customer name

Type of Service

Payment 1 date

Payment 1 amount

Payment 2 date

Payment 2 amount

Payment 3 date

Payment 3 amount

Payment 4 date

Payment 4 amount

What I want to do is sumifs the table based on multiple criteria. For example:

Type of Service	Month 1	Month 2	Month 3	Month 4
Service 1
Service 2
Service 3

The thing is that I do not want to write 4 sumifs (in this case, but in fact I have more that 4 sets of date:value columns).

I was thinking of creating a new table where I could put all the columns below each other (in one table with 4 columns - Customer name, Type of Service, Date and Payment) but the table should be dynamically created, meaning that it should be expanded dynamically with the new entries in the original table (i.e. if the original table has 200 entries, this would make the new table with 4x200=800 entries, if the original table has one more record then the new table should have 4x201=804 records).

I also checked the PowerQuery option but could not get my head around it.

So any help on the matter will be highly appreciated.

Thank you.

 

[arthurbr]

With a little tweaking for the headers a Pivot Table should work.
Alos see 5 Reasons to Use an Excel Table as the Source of a Pivot Table