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

Ilu

New Member
Joined
Sep 9, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

i am working with two tables.
Table "Sum" looks like this.
Priority07/08/202408/08/202409/08/202410/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
Priority07/08/202408/08/202409/08/202410/08/2024
2020020
10101010
Low1111
Low1111
Low1111
Low1111
Low5555
Low1111
9999
1111
12121212
1111
High4444
High6666
High1111
High2222
High1111
High2222
High5555
High1111


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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
Excel Formula:
=SUMIF(INDEX(TableRaw,,1),$A2,TableRaw[07-08-2024])
 
Upvote 0
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.
Excel Formula:
=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?
 
Upvote 0
Try this
Excel Formula:
=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
Excel Formula:
=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)))
 
Upvote 0

Forum statistics

Threads
1,224,811
Messages
6,181,080
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top