I am looking for easier way to SUM

MAP

Active Member
Joined
Mar 22, 2007
Messages
317
Office Version
  1. 2007
Platform
  1. Windows
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.
 
One way is to use an indicator header and you can grab a continuous range using SUMIF:
Book1
BCDEFGHIJKLMNOP
5
618xxxx
716634107196774
Sheet2
Cell Formulas
RangeFormula
B6B6=SUMIF(D6:P6,"x",D7:P7)
 
  • Like
Reactions: MAP
Upvote 0
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.
 
Upvote 0
Try.
Excel Formula:
=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.
 
Upvote 0
.. 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
ABCDEFGHIJKLMNOPQR
65JanFebMarApr
66RegionFYMJan ProductionJan SalesJan ProfitFYMFeb ProductionFeb SalesFeb ProfitFYMMar ProductionMar SalesMar ProfitFYMApr ProductionApr SalesApr Profit
67Region 1Production YTD $ 778.00X1195237140X2199283298X3145173266X4239267247
68Region 2Production YTD $ 977.00Y1109387504Y2299456502Y3000Y4569569485
MAP
Cell Formulas
RangeFormula
B67:B68B67="Production YTD "&TEXT(SUMIF(D$66:AV$66,"*Prod*",D67:AV67),"$ ##0.00")
 
Upvote 0
Thank you, Peter_SSs, HongRu, StephenCrump, StephenCrump.
Your knowledge, your ideas, and your assistance are greatly appreciated
 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top