Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

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?

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

Title | # Comments | Views | Activity |
---|---|---|---|

Excel Pivot Chart - Need to show sum of totals | 3 | 18 | |

Excel formula Sumif not working | 4 | 28 | |

Copy a range from 1..n excel sheets to one destination sheet | 2 | 31 | |

Need VBA to copy data from one worksheet to main worksheet | 7 | 14 |

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