Sum horizontal cells with calculation

[victoria86]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]Current Month[/TD]
[TD]14%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]57%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]Next Month[/TD]
[TD]86%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]43%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]Week Commencing[/TD]
[TD]31/12/2017[/TD]
[TD]07/01/2018[/TD]
[TD]14/01/2018[/TD]
[TD]21/01/2018[/TD]
[TD]28/01/2018[/TD]
[TD]04/02/2018[/TD]
[/TR]
[TR]
[TD]Volume[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD]400[/TD]
[TD]500[/TD]
[TD]600[/TD]
[/TR]
</tbody>[/TABLE]

Hi there

In the table above I would like to convert volume data by week into month. The first 3 rows are (hidden) formulae, the fourth row is fixed and the fifth is the input (both visible). So in another cell somewhere else in the sheet, I want the result to be 1271 for Jan i.e. (100*86%)+(200*100%)+(300*100%)+(400*100%)+(500*57%). I can only think of a way to do this by using extra calculation cells but wondered if there was a direct way?

Your help is much appreciated!

Thanks
Victoria

 

[wideboydixon]

A couple of SUMPRODUCTs should see you through:

Book1
	A	B	C	D	E	F	G
1	Month	Dec	Jan	Jan	Jan	Jan	Feb
2	Current Month	14%	100%	100%	100%	57%	100%
3	Next Month	86%	0%	0%	0%	43%	0%
4	Week Commencing	31/12/2017	07/01/2018	14/01/2018	21/01/2018	28/01/2018	04/02/2018
5	Volume	100	200	300	400	500	600
6
7	Month	1
8	Result	1271
Sheet2

Cell Formulas
Range	Formula
B8	=SUMPRODUCT($B5:$G5,$B3:$G3,--($B1:$G1=TEXT(EOMONTH(DATE(2017,$B$7,1),-1),"mmm")))+SUMPRODUCT($B5:$G5,$B2:$G2,--($B1:$G1=TEXT(DATE(2017,$B$7,1),"mmm")))

WBD

 

[victoria86]

Great thank you, works perfectly!