Sum column by priority (Y-axis) and date (Y-axis)

[Ilu]

Hi,

i am working with two tables.
Table "Sum" looks like this.

Priority	07/08/2024	08/08/2024	09/08/2024	10/08/2024
High	5​	10​	8​	12​
Medium	10​	20​	15​	27​
Low	30​	15​	27​	13​
none	50​	81​	38​	67​

The Table "Raw" looks like this

Priority	07/08/2024	08/08/2024	09/08/2024	10/08/2024
20	20	0	20
10	10	10	10
Low	1	1	1	1
Low	1	1	1	1
Low	1	1	1	1
Low	1	1	1	1
Low	5	5	5	5
Low	1	1	1	1
9	9	9	9
1	1	1	1
12	12	12	12
1	1	1	1
High	4	4	4	4
High	6	6	6	6
High	1	1	1	1
High	2	2	2	2
High	1	1	1	1
High	2	2	2	2
High	5	5	5	5
High	1	1	1	1

obviously both table continues on the right side. Table "Raw" also continues below as it is just an example.

Table "Raw" gets filled for every day automatically.

In Table "SUM" i now need to sum the complete column based on the priority on the left and the date on the top.

I thought about a combination of Sumproduct and index-match, but didn't get it quiet right.

Hope someone can help me out here.

Thanks!

 

[kvsrinivasamurthy]

Convert the raw data into a table say by name "TableRaw". rows and columns added will be automatically taken care .
select range A8:E28.
Insert --> Table
Change the name of table . Pl see left end of ribbon.
In B2 copy to full range.

=SUMIF(INDEX(TableRaw,,1),$A2,TableRaw[07-08-2024])

 

[Ilu]

Convert the raw data into a table say by name "TableRaw". rows and columns added will be automatically taken care .
select range A8:E28.
Insert --> Table
Change the name of table . Pl see left end of ribbon.
In B2 copy to full range.

=SUMIF(INDEX(TableRaw,,1),$A2,TableRaw[07-08-2024])

Thanks a lot for you help, unfortunately this causes that the "TableRaw[07-08-2024])" is a relative value and always added 1 if i extend the cells to the right. But in some cases its necessary that the formula really looks up the date thats above. Because its not always the next day.

For example if i copy your code but the date above changes to 08/08/2024 this will end up in the wrong information.

Can you help me out with this?

 

[kvsrinivasamurthy]

Try this

=SUMIF(INDEX(Sheet2!$A$9:$H$29,,1),$A2,INDEX(Sheet2!$A$9:$H$29,,MATCH(B$1,Sheet2!$A$8:$H$8,0)))

or

=SUMIF(INDEX(Sheet2!$A$9:$H$29,,1),$A2,INDEX(Sheet2!$A$9:$H$29,,MATCH(TEXT(B$1,"dd-mm-yyyy"),Sheet2!$A$8:$H$8,0)))