# Thread: Formula to sum through a specified year

1. Registered User
Join Date
Mar 2005
Posts
25

## Unanswered: Formula to sum through a specified year

Hello all,

I'm sure this has been posted before be was not able to find any prior threads. Attached is an example data file. What i'm trying to accomplish is located on sheet2. Sheet 2 contains two columns (Cumulative total & Year). I am trying to use the data located on sheet1 to sum in the cumulative total column based on the year identified in sheet2.

So for example, if the year in sheet2!B2 is 1995, sheet2!A2 should be a cumulative total of all prior years through 1995. If sheet2!B2 is 1994, then the sheet2!A2 total should equal the sum of all prior years through 1994.

I haven't had much luck in trying to crack this one, so any help would be greatly appreciated!

2. Registered User
Join Date
Mar 2005
Posts
25
I was finally able to figure it out. Using the following as an array formula, I was able to achieve my desired results. Posting in case anyone happens to run into the same requirements:

{=SUM(IF(Sheet1!\$C\$1:\$G\$1<=\$B2,Sheet1!\$C\$2:\$G\$2,0) )}

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•