Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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:


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:

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:


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:

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .