Sumifs combined with index/match

[timtim91]

Hello,

Can someone please help me setup the formula which would automatically sum the values in columns (A,B,C,A,B) per date in Table 1, and output the result in Table 2 (in A,B,C columns) as seen in yellow highlighted solution in attached file.

For example Table 1 has two A columns, Table 2 has only one A column. The sum of two A columns should be in Table 2.

I was trying to set up with sumifs and index/match functions but perhaps there is a better/ easier method.

Thank you!

[Pete_UK]

Use this formula in J3:

=SUMIFS($C3:$G3,$C$2:$G$2,J$2)

then you can copy it across and down as required.

Hope this helps.

Pete

[timtim91]

Apologies, perhaps i wasnt very clear, the columns in Table 1 can change hence i'd need sth which would always find a column A,b or C in Table 1 and sum it. Only columns in Table 2 are static. Hence i was using sumifs with index and match.

[Pete_UK]

The formula looks at the headings in row 2 of columns C to G and compares them with the heading in column J (and K and L when copied across), so I don't see what the problem is.

Pete

[sandy666]

is this acceptable?

With Power Query

Date A B C A2 B3 01/01/2022 1 2 3 4 5 01/02/2022 1 2 3 4 5 01/03/2022 1 2 3 4 5 01/04/2022 1 2 3 4 5 01/05/2022 1 2 3 4 5 01/06/2022 1 2 3 4 5 01/07/2022 1 2 3 4 5 01/08/2022 1 2 3 4 5 01/09/2022 1 2 3 4 5	Date A B C 01/01/2022 5 7 3 01/02/2022 5 7 3 01/03/2022 5 7 3 01/04/2022 5 7 3 01/05/2022 5 7 3 01/06/2022 5 7 3 01/07/2022 5 7 3 01/08/2022 5 7 3 01/09/2022 5 7 3
Date C B A2 A B3 01/01/2022 3 2 4 1 5 01/02/2022 3 2 4 1 5 01/03/2022 3 2 4 1 5 01/04/2022 3 2 4 1 5 01/05/2022 3 2 4 1 5 01/06/2022 3 2 4 1 5 01/07/2022 3 2 4 1 5 01/08/2022 3 2 4 1 5 01/09/2022 3 2 4 1 5	Date A B C 01/01/2022 5 7 3 01/02/2022 5 7 3 01/03/2022 5 7 3 01/04/2022 5 7 3 01/05/2022 5 7 3 01/06/2022 5 7 3 01/07/2022 5 7 3 01/08/2022 5 7 3 01/09/2022 5 7 3

Excel table not tolerate the same headers so there is A,A2 etc.

[timtim91]

Thank you Pete & Sandy. Pete's solution will do for now!

[sandy666]

sure,
have a nice day

[Pete_UK]

Thanks for the rep, and if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete