Sum horizontal cells with calculation

[victoria86]

Month	Dec	Jan	Jan	Jan	Jan	Feb
Current Month	14%	100%	100%	100%	57%	100%
Next Month	86%	0%	0%	0%	43%	0%
Week Commencing	31/12/2017	07/01/2018	14/01/2018	21/01/2018	28/01/2018	04/02/2018
Volume	100	200	300	400	500	600

<tbody>
</tbody>

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!