Sum a column then multiply it depending on value of a cell

[SandroUK]

Hi Guys,

I am working on something and I am a bit stuck, I am trying to sort this out without VBA as the file will be shared with other people that may not be savvy about any "warning" prompted from Excel with Macros.

I have the below example, so I need to sum the total of for example "Monday" column per the value matching A (which could be B) so in my head I came up with some stuff like this:

=SUMPRODUCT(B7:B18,SUMIF(B6,"A",B2),SUMIF(B6,"B",B3))

Which isn't working, I may thinker with VBA in case this is out of my reach with normal formulas, I am yet to explore arrays in formulas so not sure if with {} magic anything can be done

VBA tips is fine too if you have any quick ideas!

Thank you!

A​	B​	C ​	D​	E​	F​	G​	H​	I​
Menu A	€ 20.00
Menu B	€ 30.00
	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Cover Offset
Menu	a	b	b	a	a	a	b	B
Jan	63​	52​	36​	124​	109​	97​	80​	10​
Feb	54​	71​	63​	30​	86​	125​	127​	0​
Mar	80​	30​	111​	80​	84​	63​	97​	0​
Apr	112​	33​	81​	120​	107​	102​	112​	50​
May	115​	42​	127​	96​	79​	76​	47​	0​
Jun	77​	64​	53​	89​	118​	127​	91​	0​
Jul	72​	115​	83​	68​	110​	42​	83​	0​
Aug	106​	81​	59​	78​	70​	126​	67​	60​
Sep	113​	61​	50​	53​	32​	53​	67​	0​
Oct	74​	34​	112​	47​	128​	72​	33​	0​
Nov	87​	102​	50​	60​	116​	68​	119​	0​
Dec	60​	112​	80​	64​	86​	39​	107​	100​
	1013​	797​	905​	909​	1125​	990​	1030​	220​

 

[Fluff]

Hi & welcome to MrExcel.
Is this what you are after

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Menu A	€ 20.00		Monday	20260
3	Menu B	€ 30.00
4
5		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Cover Offset
6	Menu	a	b	b	a	a	a	b	B
7	Jan	63	52	36	124	109	97	80	10
8	Feb	54	71	63	30	86	125	127	0
9	Mar	80	30	111	80	84	63	97	0
10	Apr	112	33	81	120	107	102	112	50
11	May	115	42	127	96	79	76	47	0
12	Jun	77	64	53	89	118	127	91	0
13	Jul	72	115	83	68	110	42	83	0
14	Aug	106	81	59	78	70	126	67	60
15	Sep	113	61	50	53	32	53	67	0
16	Oct	74	34	112	47	128	72	33	0
17	Nov	87	102	50	60	116	68	119	0
18	Dec	60	112	80	64	86	39	107	100
19		1013	797	905	909	1125	990	1030	220
Main

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT((B5:I5=D2)*(B7:I18))*IF(INDEX(B6:I6,MATCH(D2,B5:I5,0))="A",B2,B3)
B19:I19	B19	=SUM(B7:B18)

 

[SandroUK]

Hi Fluff, I have used IFs after all!

I have done several full courses in 2020 / 2021 however having not used it all until now I feel like I have to cover most of them again, especially my two VBA courses!

Thank you for the reply!

 

[Fluff]

Glad you sorted it & thanks for the feedback.