I am looking for easier way to SUM

[MAP]

consider the following situation I have in one of my worksheets where I am trying to add various cells in the same row:
="Production YTD "&TEXT(SUM(D67,H67,L67,P67,T67,X67,AB67,AF67,AJ67,AN67,AR67,AV67),"$ ##0.00")

Is there a way to simplify the sum of multiple cells in the same row or in the same column without having to list each cell location. note that the cells are not consecutive.

 

[Cubist]

One way is to use an indicator header and you can grab a continuous range using SUMIF:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
5
6	18		x				x				x				x
7			1	6	6	3	4	10	7	1	9	6	7	7	4
Sheet2

Cell Formulas
Range	Formula
B6	B6	=SUMIF(D6:P6,"x",D7:P7)

 

[StephenCrump]

Another way:

=SUMPRODUCT(D67:AV67,--(0=MOD(COLUMN(D67:AV67)-COLUMN(D67),4)))

This is not as robust as @Cubist's approach. It relies on the values appearing in every 4th column, and will produce wrong results if you vary this, e.g. by inserting or deleting columns.

 

[HongRu]

Try.

=SUMPRODUCT(D67:AV67*NOT(MOD(COLUMN(D67:AV67),4)))

Note:
I don't sure the formula is a CSE (Ctrl+Shift+Enter) formula or not.
And instead of just pressing Enter, maybe you should press Ctrl+Shift+Enter to complete the formula.

 

[Peter_SSs]

.. or often in situations like this there are column headings with something in common without being identical as in @Cubist's suggestion. In such a circumstance you can use wildcards in the SUMIF so it might be something like this. If you are able to use headings to locate the correct columns, as @StephenCrump has indicated, the formula will be more robust if data is inserted/deleted/moved.

25 02 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
65			Jan				Feb				Mar				Apr
66	Region		FYM	Jan Production	Jan Sales	Jan Profit	FYM	Feb Production	Feb Sales	Feb Profit	FYM	Mar Production	Mar Sales	Mar Profit	FYM	Apr Production	Apr Sales	Apr Profit
67	Region 1	Production YTD $ 778.00	X1	195	237	140	X2	199	283	298	X3	145	173	266	X4	239	267	247
68	Region 2	Production YTD $ 977.00	Y1	109	387	504	Y2	299	456	502	Y3	0	0	0	Y4	569	569	485
MAP

Cell Formulas
Range	Formula
B67:B68	B67	="Production YTD "&TEXT(SUMIF(D$66:AV$66,"*Prod*",D67:AV67),"$ ##0.00")

 

[MAP]

Thank you, Peter_SSs, HongRu, StephenCrump, StephenCrump.
Your knowledge, your ideas, and your assistance are greatly appreciated

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.