Advanced Transposing

With your first table on Sheet1, starting in Cell A1, and your second table on Sheet2 starting in A1, enter this into Sheet2, cell C2:

=SUMIFS(INDEX(Sheet1!$C:$D,,MATCH($B2,Sheet1!$C$1:$D$1,FALSE)),Sheet1!$A:$A,$A2,Sheet1!$B:$B,C$1)

and copy down and across to match your labels on Sheet2. This relies on all combinations of Client and Date existing on both sheets: in column A and row 1 on Sheet1, and in columns A and B on Sheet2.

Note that this will return 0 for any product that does not exist for a client/date combo.

This part:

INDEX(Sheet1!$C:$D,,MATCH($B2,Sheet1!$C$1:$D$1,FALSE))

chooses the column (C or D) based on the date in B2 matching the header dates in Sheet1 C1:D1 - so it passes either column C or D to the SUMIFS - the rest is simply the standard SUMIFS, with the correct $ing to allow copying into a table.

=SUMIFS(C or D,Sheet1!$A:$A,$A2,Sheet1!$B:$B,C$1)

I have attached a working example using your data. Insert rows and columns as needed to expand the ranges...

Give this one a try:

Hi Edward,

There is a newer tool in Excel called Power Query that has a tool called Fill Down. Your last sentence above seems like this is what you are looking for. Without a sample file it is hard to know exactly what you want.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.