# Calculate totals based on date

1. ## Calculate totals based on date

Good day everyone. This forum has become invaluable in my education of Excel. I now have a question I don't know the answer to.

I am trying to create a spreadsheet that adds up a progressive total of tasks done compared to what should be done by that date. For example if today is January 3rd I want the "should be done" total to be a cumulative total of Jan 1, 2 and 3. However, I don't want it to add up the information for the rest of the year in the succeeding cells. Basically I want it to automatically update the formula to cumulate the total based on what the date is.

I have attached a PDF that illustrates what I am looking to do. This is a sample and my actual file has the dates filled to the end of the year.

All help would be most appreciated.

Thank you

Shane

2. Originally Posted by shane24
Good day everyone. This forum has become invaluable in my education of Excel. I now have a question I don't know the answer to.

I am trying to create a spreadsheet that adds up a progressive total of tasks done compared to what should be done by that date. For example if today is January 3rd I want the "should be done" total to be a cumulative total of Jan 1, 2 and 3. However, I don't want it to add up the information for the rest of the year in the succeeding cells. Basically I want it to automatically update the formula to cumulate the total based on what the date is.

I have attached a PDF that illustrates what I am looking to do. This is a sample and my actual file has the dates filled to the end of the year.

All help would be most appreciated.

Thank you

Shane
try this

=SUMIF(A2:A22,"<="&TODAY(),B2:B22)

Regards.

3. Originally Posted by shane24
Good day everyone. This forum has become invaluable in my education of Excel. I now have a question I don't know the answer to.

I am trying to create a spreadsheet that adds up a progressive total of tasks done compared to what should be done by that date. For example if today is January 3rd I want the "should be done" total to be a cumulative total of Jan 1, 2 and 3. However, I don't want it to add up the information for the rest of the year in the succeeding cells. Basically I want it to automatically update the formula to cumulate the total based on what the date is.

I have attached a PDF that illustrates what I am looking to do. This is a sample and my actual file has the dates filled to the end of the year.

All help would be most appreciated.

Thank you

Shane
HI,

=SUMPRODUCT(--(A1:A100<TODAY())*(--(A1:A100<>"")*(B1:B100)))

should total the B column for you, two other copies of that for C1:C100 and D1:D100 should do the rest, however, the range 1:100 will need to be set to suit your data.

hth
---

4. You guys are wonderful and fast to respond. Just great I tell ya.

I have posted a sample of how I plugged in the formulas that each of you have provided. One seems to work and the other doesn't. If you have a moment please take a look and provide feedback.

Thanks,
Shane

5. Originally Posted by shane24
You guys are wonderful and fast to respond. Just great I tell ya.

I have posted a sample of how I plugged in the formulas that each of you have provided. One seems to work and the other doesn't. If you have a moment please take a look and provide feedback.

Thanks,
Shane
Bryan's formula will work with a little change.

=SUMPRODUCT(--(A1:A100<=TODAY())*(--(A1:A100<>"")*(B1:B100)))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1