Summing data in rows based on horizontal and vertical criteria

Question

I have a dataset in the below format:

           Date 1   Date 1   Date 1  Date 2   Date 2   Date 3   Date 3
Product 1    10       20       10      5       10       20       30
Product 2    5        5        10      10      10       5        30
Product 3    30       10       5       10      30       30       40
Product 4    5        10       10      20      5        10       20

and I am trying to sum the sales of the products by the date, to create the below:

           Date 1   Date 2   Date 3  
Product 1    40       15       50            
Product 3    45       40       70       
Product 4    25       25       30 
Product 2    20       20       35     

The products in the second table will often be in a different order, so a simple SUMIF will not suffice.

I've attempted a combination of SUM, INDEX and MATCH, as well as SUM with nested IF function, but no amount of Googling or trial and error is getting me there. I keep just bringing back the values in one cell, but not managing to sum.

Solution

With the following setup:

I used the following formula

=SUMIF($B$1:$H$1,B$10,INDIRECT("$B" & MATCH($A11,$A$1:$A$5,0) & ":$H" &MATCH($A11,$A$1:$A$5,0)))

To get what was wanted. I put the formula in B11 and then copied across and Down

Comments

[Lottie]

I didn't realise I was jumping into such a minefield. I thought I'd sign up as on top of 'general Excel' I also use VBA, HTML, CSS & JS so thought SO might be a bit of a one stop shop for any questions I might have!

[Richard Erickson]

@pnuts Thanks for pointing that out. Do you if it would be possible to include that guidance on the Excel tag?