Relative Sum based on all the cells referenced in another Cell

[Ninja97]

I have no idea how to search for what I'm about to explain, so I apologize if this is already covered elsewhere. However, there's a good chance it hasn't.

I have a cell that sums a bunch of selected cells in the spreadsheet. Here's an example. What I have in cell BG62: =R11+R17+X15+AD11+AD18+R36+X45+AJ38+AJ40+AJ46+AJ47+R65

Each of these cells referenced in BG62 is a "profit" number from a sale. The above is just a small snippit. There are typically MANY MORE cells being added than what you see above. The cell immediately to the right of each cell contains the total retail dollars of the sale.

I want to have a cell that adds up the list of cells that are all immediately to the right of the cells referenced in BG62. How can I do this, aside from manually - like an animal?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is there any text beside the original cells that identifies them as "profit" cells?
Could you use XL2BB to post a mini sheet showing the range Q10:AK66? (alter any sensitive data first)

 

[Ninja97]

PTOP23.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
9	Walk-In, Cheap Twin	10:00am		Nope	$0.00						$0.00						$0.00		Walk-In, Bonnie Royer & Husband	9:30am	Menekaunee Full		$62.25	#					$0.00						$0.00		#DIV/0!	#DIV/0!	$374.52											Radio	$0.00
10					$0.00						$0.00						$0.00		Walk-In, Lady wants Camper Queen	10:00am		Nope	$0.00						$0.00						$0.00															Time Saver	$0.00
11	Walk-In, Lyle McGuire	10:30am	Menekaunee Twin		$45.50	150	Walk-In, Susan Thompson	10:00am	BR Select Medium w/Box		$414.00	#					$0.00		Walk-In, Jenny Corey	10:30am	Escanaba Queen		$834.00	#					$0.00						$0.00															Cell Phones	$135.00
12					$0.00						$0.00						$0.00		Walk-In, Dude for Twin for 19yr old son	11:00am		Nope	$0.00						$0.00						$0.00															Vendor Surcharges	$152.55
13	Walk-In, Bill Younger	11:30am	Chambers Island Queen		$173.42	400	Walk-In, Girl & Daughter, need Dark Moon	10:30am		Nope	$0.00						$0.00						$0.00						$0.00						$0.00															Zoho.com	$16.00
14					$0.00						$0.00						$0.00		Walk-In, Julie - World Famous Queen, offered her an extra 10% off	12:00pm		Nope	$0.00						$0.00						$0.00															Email Acct.	$6.00
15					$0.00						$0.00		Walk-In, Keith Kesler	1:30pm	World Famous Queen		$371.25	##					$0.00						$0.00						$0.00															NMG Dues	$299.00
16					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00															Montage	$0.00
17	Walk-In, Dude, California King	2:15pm		Nope	$0.00		Walk-In, Cheryl Redpath	12:30pm	Escanaba Twin		$695.50	#	Walk-In, Lady from River Drive, Queen Escanaba	2:30pm		Nope	$0.00						$0.00						$0.00						$0.00															Facebook	$128.40
18					$0.00						$0.00						$0.00		Walk-In, Karen & Tom Odegaard	2:30pm	Custom RV King World Famous		$400.25	#					$0.00						$0.00															Gr.Web.Social Media	$0.00
19					$0.00						$0.00						$0.00		Walk-In, Dude & Son	2:45pm	Cheap Full	Nope	$0.00						$0.00						$0.00															Premium Waters	$32.69
20					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00															Mare Hare	$524.00
21					$0.00		Walk-In, Lady & Grandaughter	4:00pm	Queen Twin Cities	Nope	$0.00						$0.00						$0.00						$0.00						$0.00															Billboards	$1,276.84
22					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00															Deliveries	$40.00
23					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00															Men.Cty.Journal	$154.00
24					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00															Skagit	$199.00
25					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00															Eagle-Herald	$246.24
26					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00															Month.Exp.	$4,828.76
27																																																		One-Off Expenses for August:
28																																					WEEK 2													1	$39.71	Menards - Coffee Supplies
29	Tuesday 8/08/23						Wednesday 8/09/23						Thursday 8/10/23						Friday 8/11/23						Saturday 8/12/23						Sunday 8/13/23						W	A	S	S	P	P								2
30	W	A	S	S	P		W	A	S	S	P		W	A	S	S	P		W	A	S	S	P		W	A	S	S	P		W	A	S	S	P		15	0	0	9	$5,382.79			15						3
31	4	0	0	1	$1,030.50		2	0	0	1	$457.90		2	0	0	1	$773.67		3	0	0	2	$744.75		4	0	0	4	$2,375.97		0	0	0	0	$0.00															4
32					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00		Appointments	Shows	Profit											5
33					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00		to Shows:	to Sales:	per Sale:											6
34					$0.00						$0.00		Walk-In, Hot Chick, Firm Beautyrest Queen or King	9:00am		Nope	$0.00						$0.00						$0.00						$0.00		#DIV/0!	#DIV/0!	$598.09											7
35					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00															8
36					$0.00		Walk-In, Rebecca Hruska	10:00am	Twin Cities Full w/Bunky		$457.90	#					$0.00		Walk-In, Scott Christiensen, Stephenson Island Queen w/PLTFRM	10:00am	Meadowbrook Queen w/PLTFRM		$419.00	#					$0.00						$0.00															9
37					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00															10
38					$0.00						$0.00						$0.00						$0.00		Walk-In, Dude & Jennifer Kirt	11:00am	Escanaba Queen w/Dreamcool Protector		$886.00						$0.00															Month.Exp.	$39.71
39	Walk-In, Andrew & Ricci Heimbuch	12:00pm	Dark Moon King w/DF CP & DF SH		$1,030.50	##					$0.00						$0.00						$0.00						$0.00						$0.00
40					$0.00						$0.00						$0.00						$0.00		Walk-In, Suzy Miron	11:45am	King Active Geldough Pillow		$55.00						$0.00															Total Exp.	$4,868.47
41					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00
42					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00																Checks Paid to
43	Walk-In, Russ & Bev	2:00pm	Full, Firm'ish? Bev had neck surgery	Nope	$0.00		Walk-In, Steve Nerat, Camper Queen, $649	2:00pm		Nope	$0.00						$0.00						$0.00						$0.00						$0.00																self out of this
44					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00																month's Profits:
45					$0.00						$0.00		Walk-In, Melody Reif	3:00pm	BR Select Queen, w/PLTFRM & sheets & pillows		$773.67	##	Walk-In, Dude looking for twin for teenage sone	3:00pm		Nope	$0.00						$0.00						$0.00
46					$0.00						$0.00						$0.00						$0.00		Walk-In, Nicolet Hamilton	2:30pm	Stephenson Island Queen		$276.75						$0.00																		Gross Walk-In
47	Walk-In, Lady for a Full, Firm	4:00pm		Nope	$0.00						$0.00						$0.00						$0.00		Walk-In, Nicole Bebo	3:00pm	CrescentMoon King w/PLTFRM		$1,158.22						$0.00																	Cost	Profit
48					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00																Jen FB:	$0.00	$520.15
49	Walk-In, Dude	5:00pm	Queen Dark Moon w/Adj.Bse	Nope	$0.00						$0.00						$0.00		Walk-In, Jacob DeGrand	5:00pm	Stephenson Island Queen		$325.75	#					$0.00						$0.00																Shane-IDS:	$0.00	$0.00
50					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00																FB.Business:	$128.40	$0.00
51					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00																OfferUp:	$0.00	$0.00
52					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00																Walk-In:	$0.00	$5,670.09
53					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00																Google:	$0.00	$1,030.50
54																																																			iNET:	$0.00	$0.00
55			219																																		WEEK 3														Restaurant:	$0.00	$442.00
56	Tuesday 8/15/23						Wednesday 8/16/23						Thursday 8/17/23						Friday 8/18/23						Saturday 8/19/23						Sunday 8/20/23						W	A	S	S	P	P	S								Craigslist:	$0.00	$0.00
57	W	A	S	S	P		W	A	S	S	P		W	A	S	S	P		W	A	S	S	P		W	A	S	S	P		W	A	S	S	P		3	1	0	1	$442.00			3							FFR	$0.00	$1,158.22
58	0	0	0	0	$0.00		1	0	0	1	$442.00		0	0	0	0	$0.00		0	1	0	0	$0.00		0	0	0	0	$0.00		0	0	0	0	$0.00																	Walk-In Profit -->	$8,820.96
59					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00		Appointments	Shows	Profit
60					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00		to Shows:	to Sales:	per Sale:												Cash or Check:	$1,311.67
61					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00		0.0%	#DIV/0!	$442.00												PAYROCK
62					$0.00						$0.00						$0.00						$0.00						$0.00						$0.00																Merchant One	$6,764.54
August

Cell Formulas
Range	Formula
AR61:AT61,AR34:AT34,AR9:AT9	AR9	=AT5/AS5
AW52:AW62,AW45:AW46,AW26:AW36,AW9:AW14	AW9	=AV9
AX52:AX62,AX45:AX46,AX30:AX36,AX26:AX28,AX9:AX14	AX9	=AU9
BF12	BF12	=F2
BF21	BF21	=450+61.5+15.34+750
BF22	BF22	=10+10+10+10
AP59:AP62,AJ59:AJ62,AD59:AD62,X59:X62,R59:R62,L59:L62,AP32:AP53,AJ48:AJ53,AJ41:AJ45,AJ39,AJ32:AJ37,AD50:AD53,AD37:AD48,AD32:AD35,X46:X53,X32:X44,R37:R53,R32:R35,L40:L53,L32:L38,AP9:AP26,AJ9:AJ26,AD19:AD26,AD12:AD17,AD10,X16:X26,X9:X14,R18:R26,R12:R16,R9:R10	L9	=(M9)
L11	L11	=(M11)-104.5
L13	L13	=(M13)-226.58
R11	R11	=(S11)-(108+352)
R17	R17	=(S17)-(503.5)
X15	X15	=(Y15)-327.75
AD9	AD9	=(AE9)-137.75
AD11	AD11	=(AE11)-665
AD18	AD18	=(AE18)-498.75
BF26	BF26	=SUM(BF1:BF25)-BF2
AR57:AV57,AR30:AV30	AR30	=B31+H31+N31+T31+Z31+AF31+AL31
AY30,AY57	AY30	=B31+H31+N31+T31+Z31+AF31+AL31
BF38,BH58	BF38	=SUM(BF28:BF37)
BF40	BF40	=BF26+BF38
BH48	BH48	=AD9+R36
BG50	BG50	=BF17
L31,AP58,AJ58,AD58,X58,R58,L58,AP31,AJ31,AD31,X31,R31	L31	=SUM(L32:L53)
L39	L39	=(M39)-(807.5+75+56)
R36	R36	=(S36)-(451.25+40.85)
X45	X45	=(Y45)-(352+65+95+10.83+57+26.25+26.25)
AD36	AD36	=(AE36)-(285+95)
AD49,AJ46	AD49	=(AE49)-223.25
AJ38	AJ38	=(AK38)-(665+48)
AJ40	AJ40	=(AK40)-45
AJ47	AJ47	=(AK47)-(830.78+110)
BH52	BH52	=L11+L13+R11+R17+X15+AD11+AD18+X45+AD36+AD49+AJ38+AJ40+AJ46
BH53	BH53	=L39
BH55	BH55	=R65
BH57	BH57	=AJ47
BG60	BG60	=L11+L13+AD9+L39
BG62	BG62	=R11+R17+X15+AD11+AD18+R36+X45+AJ38+AJ40+AJ46+AJ47+R65

 

[Peter_SSs]

Thanks for the sample data. Unfortunately, I cannot see any logic to what you are doing.

BG62: =R11+R17+X15+AD11+AD18+R36+X45+AJ38+AJ40+AJ46+AJ47+R65

Why those cells and not also, say, L39 or AD36 or AD49 or even X31 .. etc?

 

[Ninja97]

The cells that are not listed are instances where the customer paid by either cash, check, or financing. I didn't offer my spreadsheet in the first place, for a reason.

But, (and please don't read any rudeness in this question) Is my question not a valid question on its own without having to provide full context? I provided exactly every bit of information required to answer my question. It is highly likely that what I'm looking for cannot be done, and I'm prepared for that answer - I just wanted to be sure.

I'm guessing I already have my answer, and you wanted more context so that you could find a better (more realistic) way to accomplish what I need, rather than what I want. Your efforts are not unappreciated, especially since you're the only one who has taken any interest in my problem

Thank you.

 

[Peter_SSs]

please don't read any rudeness in this question

No offence taken.

Is my question not a valid question on its own without having to provide full context?

It is, but with nearly 20 years experience in answering a lot questions in this forum I have found that in 95%+ cases when the full context is revealed, the question can be answered directly or a very simple alternative approach offered. In most of those 95%+ cases if attempting to answer without the full context, time is spent providing suggestions that turns out to be time wasted.

It is highly likely that what I'm looking for cannot be done,

I don't believe that it can with standard worksheet formulas.
However, if you are happy to consider a user-defined function (post back if you need instructions to implement it) you could use this one.

Function SumOffset(rBase As Range, Optional oSet As Long = 1) As Double
  Dim cAddr As Variant

  For Each cAddr In Split(Mid(rBase.Formula, 2), "+")
    SumOffset = SumOffset + Range(cAddr).Offset(, oSet).Value
  Next cAddr
End Function

On the sample worksheet, using this formula ..

=SumOffset(BG62)

.. produces the result 11,824

The cells that are not listed are instances where the customer paid by either cash, check, or financing.

So why not consider including a column relating to how the customer paid and then you could probably use a simpler SUMIFS for both the original sum and the offset sum?


BTW, thanks for updating your version details.

 

[Ninja97]

Perfect!

I've implemented it and it appears to work flawlessly. I've used VBA to write some seriously complex macros, but never to make a simple function. Is there ANYTHING Excel can't do? Thanks to you, I feel my Excel skills have now become fully weaponized.

Thank you, Peter.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.
(Just needed full context. )