Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

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

1 Solution

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.

Tackle projects and never again get stuck behind a technical roadblock.

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