# How to sum based on a given date

#### mart_mrexcel

##### Active Member
I have a heading date from d2 to O2 (ie. Jan 31 2016 - Dec 31 2016)and it has a value in each date located at cell D3 to O3. How can I sum the total based on a given date. Example if I put July 31 2016, it will sum from Jan - July 31 only

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi. You need to elaborate on at least the following three questions in order for us to help you:

(i) Are the dates in D2:O2 entered as date values, i.e. ‘Jan 31 2016’ = 42400, or are they entered as plain text, i.e. "Jan 31 2016"?

(ii) Should the summation always start from the very first date in the range, i.e. D2 (no matter what date value it has)?

(iii) What if the given date falls between two dates in the D2:O2, should the sum then include the previous or the following date?

do you always want to sum from 1st jan 2016
use a sumifs()

=SUMIFS(D3:O3,D2:O2,">="&DATEVALUE("1/1/16"),D2:O2,"<="&DATEVALUE("1/7/16"))

or you can use a cell to put the "to" date

D2:O2 entered is always D2:O2 entered

Sorry, I don't understand your post

Last edited:
A2: 2016-07-31

In B2 enter:

=SUMIFS(\$D3:\$O3,\$D\$2:\$O2,"<="&\$A2)

which essentially what etaf also suggests.

Replies
3
Views
128
Replies
17
Views
381
Replies
11
Views
385
Replies
12
Views
554
Replies
8
Views
174

1,203,029
Messages
6,053,125
Members
444,640
Latest member
Dramonzo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back