Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.

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 |
---|---|---|---|

Longer drop-down menu for Data Validation list in Excel | 8 | 27 | |

Array problem | 6 | 49 | |

Determine dependencies | 3 | 21 | |

Delete row if does not start with 0 | 43 | 38 |

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