Sum Index Match Formula help

[riley2606]

Hi,

I am working on email platform scheduling table.
I want a table like the table at the bottom of the post to the side of the table below which has rows of the days where in the adjacent cell I enter a platform name and in the adjacent cell next to it there will be a formula that sum's up the total volume from which platform I enter in the box. There will be a new sum box for each week. It was suggested I could use the index, match or offset formulas in some way but I am yet to figure it out.

[TABLE="class: grid, width: 396"]
<colgroup><col width="88" style="width:66pt" span="2"> <col width="110" style="width:83pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 88"][/TD]
[TD="width: 88"][/TD]
[TD="width: 110"]Monday[/TD]
[TD="width: 110"]Tuesday[/TD]
[/TR]
[TR]
[TD]10-16/07/17[/TD]
[TD]Client Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Platform[/TD]
[TD]Platform 1[/TD]
[TD]Platform 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Volume[/TD]
[TD="align: right"]40,000[/TD]
[TD="align: right"]200,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Platform[/TD]
[TD]Platform 5[/TD]
[TD]Platform 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Volume[/TD]
[TD="align: right"]65,000[/TD]
[TD="align: right"]50,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Platform[/TD]
[TD]Platform 1[/TD]
[TD]Platform 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Volume[/TD]
[TD="align: right"]30,000[/TD]
[TD="align: right"]10,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Platform[/TD]
[TD]Platform 3 [/TD]
[TD]Platform 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Volume[/TD]
[TD="align: right"]58,000[/TD]
[TD="align: right"]80,000[/TD]
[/TR]
[TR]
[TD]17-23/07/17[/TD]
[TD]Client Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Platform[/TD]
[TD]Platform 1[/TD]
[TD]Platform 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Volume[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]40,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Platform[/TD]
[TD]Platform 2[/TD]
[TD]Platform 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Volume[/TD]
[TD="align: right"]70,000[/TD]
[TD="align: right"]10,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Platform[/TD]
[TD]Platform 1[/TD]
[TD]Platform 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Volume[/TD]
[TD="align: right"]25,000[/TD]
[TD="align: right"]40,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Platform[/TD]
[TD]Platform 4[/TD]
[TD]Platform 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Volume[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]30,000[/TD]
[/TR]
</tbody>[/TABLE]

I used =SUMPRODUCT(--($K$5=$C$4:$C$14)*($C$4:$C$14)) on a previous table. I am aware the cell references now don't match. But it worked mostly fine except I needed to have a number on the end of the platform name instead of just some text for it to work.

[TABLE="class: grid, width: 365"]
<colgroup><col width="145" style="width:109pt"> <col width="110" style="width:83pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 145"]Day[/TD]
[TD="width: 110"]Platform[/TD]
[TD="width: 110"]Volumes[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]Platform 2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Also would it be possible to then have the volume cell to be conditionally formatted based on the volume and what platform it is. As different platforms have different daily volumes limit I would want the cell to have multiple conditions but I am also unsure how to do that too.

 

[njimack]

Confirm with ctrl+shift+enter:
=SUM(IF($C$1:$I$1=$K5,IF($C$3:$D$24=$L5,$C$4:$D$25)))