Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a workbook with 10 sheets starting with March and running thru December for 2012.

If I want each successive sheet to maintain a YTD total column, then what should the formula look like?

For example, if I need a formula to add cell C14 from March and cell C16 from April then what is the proper syntax?

share|improve this question

3 Answers 3

The way I handle this is to write a formula that sums "vertically", through the workbook. If you think of a normal Excel formula as being in 2D, this makes it 3D.

To do this you would need to use a consistent cell to store your info. If you have a worksheet for each month, pick one cell for your monthly total and keep it the same for every month. Let's say we choose cell C14.

Here's the formula you need to calculate YTD for December:

=SUM(Jan:Dec!C14)

I'm assuming a couple of things here.

This assumes your sheets are named Jan, Feb, Mar, Apr etc, and also that they are in that order within the workbook (ie the order is Jan, Feb, Mar, Apr etc).

For each worksheet you'll need to tweak the formula, ie:

=SUM(Jan:Jan!C14)
=SUM(Jan:Feb!C14)
=SUM(Jan:Mar!C14)
=SUM(Jan:Apr!C14)
etc
share|improve this answer

I don't think you need anything complex to accomplish what you want.

You can set the YTD cell for March to be the sum of whatever it's counting up on that sheet, then have April's YTD cell be another sum formula that starts with March's YTD column and adds the sum of April's count. Repeat this with each successive month/worksheet and you've got your running total.

Essentially you'd have:

  • March's YTD =sum(B2:B6)
  • April's YTD =sum(March!A1, B2:B6)
  • May's YTD =sum(April!A1, B2:B6)
  • etc
  • etc
share|improve this answer

To reference a cell in a different sheet, you can use the '!' syntax:

=OtherSheet!C14

Note that if the sheet name contains spaces, you'll need to single-quote it:

='Other Sheet'!C14

So, for your example, if your sheets are simply named with the month names, that'd be:

=SUM(March!C14,April!C16)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.