Sum If based on selection

[cunningAce]

Hi,

I am looking for a formula that takes the Week Number, and sums anything in the relevant column but also based on the code as in the below example.

So here I would want the total for all codes 'A1' in week '201809'?

[TABLE="width: 512"]
<colgroup><col width="64" style="width:48pt" span="8"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Code[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]201807[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]201808[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]201809[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]201810[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]Week >[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]201809[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]A1[/TD]
[TD="bgcolor: transparent, align: right"]65[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]21[/TD]
[TD="bgcolor: transparent, align: right"]26[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]B1[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B1[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]27[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C1[/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[TD="bgcolor: transparent, align: right"]28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.

 

[Weazel]

something like...

Unknown[TABLE="class: grid, width: 500"]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Code[/TD]
[TD="align: right"]201807[/TD]
[TD="align: right"]201808[/TD]
[TD="align: right"]201809[/TD]
[TD="align: right"]201810[/TD]
[TD="align: right"][/TD]
[TD]Week >[/TD]
[TD="align: right"]201809[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]A1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD]A1[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]A1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"][/TD]
[TD]B1[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]B1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]C1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]A1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=SUMPRODUCT(($B$2:$E$6),($B$1:$E$1=$H$1)*($A$2:$A$6=G2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[cunningAce]

Works perfect thanks

 

[Weazel]

you're welcome