VLOOKUP & sum over multiple sections

[julievandermeulen]

I have a worksheet that i would like a formula for. I'm having trouble figuring out the correct formula and maybe it won't even work.

In cells AA11:AA40 I would like the sum form H11:S38 depending on the text in B11:B38 & E11:E38. For example B11 has "Loops" and E11 has "Albion" so I want the sum of (H11:R11) to be entered in cell AA11. The sum of H13:R13 to be enter in column AA depending on Text in B13 & E13.

But is it possible to also have it sum from H57:R84 (up tp 30 pages) or would each page need to be calculated separately?

Bucket Punch Sheet.xlsx

1drv.ms

 

[julievandermeulen]

oh shoot. I merged cells just to get the format to work better. I created a 2nd page and unmerged cells. If I can figure out formulas. I'll figure out formating later.

 

[Cubist]

Is this what you mean?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5
6	Time In	Time Out	Field	Variety	# of Buckets
7	4:00 AM	7:00 AM	Loops	Albion	4	4	4	4						16
8			Loops	Albion	4	4	4	4						32
9			Loops	Shuksans	3	2	1							38
10	12:00 PM	12:30 PM	Farm	Sweet Sunrise	2	2								4
11	1:00 PM	2:00 PM	Jim's	Albion	3	3								6
Sheet3

Cell Formulas
Range	Formula
O7:O11	O7	=IF(B7<>"",SUM(F7:M7),SUM(F7:M7)+O6)

 

[julievandermeulen]

yes! now if there is no data in F:M can we make it so the number is 0?

 

[Cubist]

It should be giving you 0 already if there's no data.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
5
6	Time In	Time Out	Field	Variety	# of Buckets
7	0.166667	0.291667	Loops	Albion										0
8			Loops	Albion										0
9			Loops	Shuksans										0
10	0.5	0.52083333	Farm	Sweet Sunrise										0
11	0.54166667	0.58333333	Jim's	Albion										0
Sheet4

Cell Formulas
Range	Formula
O7:O11	O7	=IF(B7<>"",SUM(F7:M7),SUM(F7:M7)+O6)

 

[julievandermeulen]

it's not in O12 it just repeats the number from o11, from your example above.

 

[Cubist]

Try this:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
6	Time In	Time Out	Field	Variety	# of Buckets
7	0.166667	0.291667	Loops	Albion	4	4	4	4						16
8			Loops	Albion	4	4	4	4						32
9			Loops	Shuksans	3	2	1							38
10	0.5	0.52083333	Farm	Sweet Sunrise	2	2								4
11	0.54166667	0.58333333	Jim's	Albion	3	3								6
12														0
13														0
Sheet4

Cell Formulas
Range	Formula
O7:O13	O7	=LET(s,SUM(F7:M7),IF(s=0,0,IF(B7<>"",s,s+O6)))

 

[julievandermeulen]

Yes that worked!!!! thank you.

now 1 more question. is it possible to have a function for if the check box is marked, that it calculates. for example. if the box is marked yes. than sum(D23*AC2)+(o23*AC3) or do I need to format that differently?

 

[Cubist]

I can't see the checkbox with the online Excel version. I don't think I can assist further. You should start a new thread.

 

[julievandermeulen]

Ok. Great Thanks for all your help I really appreciate it.

 

[Cubist]

You're welcome. You should update your Excel Version in your Account Details. It helps other people provide the best solution.