Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Become a Premium Member and unlock a new, free course in leading technologies each month.

Solved

Posted on 2013-12-12

Does anyone know how to simplify this Excel formula: b12+b19+b27+b34+b42+b49+b56+b64+b71+b78+b86+b93?

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

- Help others & share knowledge
- Earn cash & points
- Learn & ask questions

5 Comments

But you may place it into some unused cell (e.g. az1) and then use just the reference to az1 (multiple times).

Is there another column that the selection of cells is based on?

For example if your data was such that you are adding all cells in column B where the value in column A had a specific value or number, you can use SumIF

SUMIF( range, criteria, [sum_range] )

For example if my data was:

Year Value

2013 1

2013 2

2011 3

2011 4

2012 5

2012 65

2013 7

2013 8

2011 9

2012 9

and I wanted to sum where the year was 2013, I would do:

=SUMIF(A2:A11, 2013, B2:B11)

(which gives me 18)

Hope that helps!

if they should be equidistant, then a formula like: =SUMPRODUCT((MOD(ROW(B12:B

where the cells are

If for example your formulas in the source cells are a sum of above eg:

B12 contains =SUM(B1:B11)

B19 contains =SUM(B13:B18) etc

Then in B94 you have the above multiple sums summated, you could change to a SUBTOTAL.

In each of the source SUMS change to SUBTOTAL

B12 contain =SUBTOTAL(9,B1:B11)

B19 contain =SUBTOTAL(9,B13:B18)

Then in B94

=SUBTOTAL(9,B1:B93)

This last SUBTOTAL will ignore other subtotals within the range.

Rather than manually replacing each of the SUMs with SUBTOTALs use the Edit - Replace function (Ctrl + h)

Find SUM(

Replace with SUBTOTAL(9,

Thanks

Rob H

eg

=SalesJan+SalesFeb+SalesMa

To define the names quickly, select the cell you wish to name, and then type the name in the box to the LEFT of the formula bar (it will currently display the cell ref).

Then, whenever you build a formula that needs that cell, click it, and the formula will use the name, rather than the cell ref.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.

If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…

- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word

Course of the Month4 days, 10 hours left to enroll

Join the community of 500,000 technology professionals and ask your questions.