A way around SUM function #REF! errors?

[MSC]

Hi, I manage a fairly large spreadsheet with a challenge: it lives on a shared drive, and a number of people with varying Excel skills have access to it and are required to update it with forecasted figures which sometimes change.

Regular use of the spreadsheet often causes my summary formulas to turn into errors.

If one of the forecasted numbers changes and now needs to move to a different week, folks will sometimes just drag that cell over to the new column. This dragging causes formula errors.
(I know what you are thinking, just ask them to NOT drag cell contents: let's just say that's not an option so I must work within these constraints).

My summary totals at the bottom require me to segment the group in a number of different ways.
But if you drag either of the yellow cells (or any other cells w/data) to a different week, that is what triggers the formula error.

How do I avoid this? thank you.

PS I have a mac, couldn't get XL2BB to work....

 

[KRice]

Have a look at this approach (in green cells) to see if it addresses the problem. I dragged the yellow cells from their original locations (blanks there now) to illustrate how the cell-by-cell formulas you show follow the moved numbers, while the SUMIF approach does not.

MrExcel_20220719.xlsx
	A	B	C	D	E	F
8		Person a	20	10	20
9		Person b	8	8	9
10		Person c	40	30	50
11		Person a	60	70	60
12		Person z	5	8	13
13		Person b	50		20	40
14		Person m	60	80	40
15		Person a		90	80	100
16
17		total a	180	170	160
18		total b	58	48	29
19	total	Person a	80	170	160
20	total	Person b	58	8	29
Sheet6

Cell Formulas
Range	Formula
C17	C17	=C8+C11+F15
D17:E17	D17	=D8+D11+D15
C18,E18	C18	=C9+C13
D18	D18	=D9+F13
C19:E20	C19	=SUMIF($B$8:$B$15,$B19,C$8:C$15)

 

[Peter_SSs]

PS I have a mac, couldn't get XL2BB to work....

That's fine, XL2BB does not work with some versions of Excel on Mac. However, it would be useful if you updated 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’)

 

[MSC]

Slow response, my apologies, but this is terrific - just what I needed, thanks.

 

[KRice]

You're welcome...I'm happy to help.

 

[Peter_SSs]

@MSC
Please note that the 'Mark as solution' option should be used to mark the actual post that contains the solution. Perhaps it was accidental but you had marked post #3 when it clearly does not answer your original question.
I have changed it to mark Kirk's post instead.