# date formulae question help!!

#### tankan

##### New Member
hi,

i'm wondeirng how i caculate the sum of dates with figures

3 columns

1st column.
Start date

2nd column
End date

3rd column
Values

the dates together refer to the financial years for australia for example

for 2001, it would be 1/7/2000 start to the 31st/6/2001 end
for 2002, it would be 1/7/2001 start to the 31st/6/2002 end

all i need in column 4 or D column would be simply check to ensure that the entire list (a range which goes to say 2000) , it would sum values which fall within these dates (financial year in the example ) above..

if it does, read across to row c (3rd column), add that value..

keep checking down the list of 2000 rows...

any help grateful

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
tankan said:
hi,

i'm wondeirng how i caculate the sum of dates with figures

3 columns

1st column.
Start date

2nd column
End date

3rd column
Values

the dates together refer to the financial years for australia for example

for 2001, it would be 1/7/2000 start to the 31st/6/2001 end
for 2002, it would be 1/7/2001 start to the 31st/6/2002 end

all i need in column 4 or D column would be simply check to ensure that the entire list (a range which goes to say 2000) , it would sum values which fall within these dates (financial year in the example ) above..

if it does, read across to row c (3rd column), add that value..

keep checking down the list of 2000 rows...

any help grateful

=SUMIF(DateRange,">="&FromDate,ValueRange)-SUMIF(DateRange,">"&ToDate,ValueRange)

thanks

could someone explain how i implement solution by Aladin..

i think the formula uses name ranges right..

ie.
=SUMIF(DateRange,">="&FromDate,ValueRange)-SUMIF(DateRange,">"&ToDate,ValueRange)

just so i'm interpeting this right.

DateRange is my what? the range of the from dates called DateRange..

&FromDate, what do i do with this one? replace with one?

ValueRange is the values that i'm asking for it to sum

and the other end is the To ranges...

Re: thanks

tankan said:
could someone explain how i implement solution by Aladin..

i think the formula uses name ranges right..

ie.
=SUMIF(DateRange,">="&FromDate,ValueRange)-SUMIF(DateRange,">"&ToDate,ValueRange)

just so i'm interpeting this right.

DateRange is my what? the range of the from dates called DateRange..

That is the range which houses the dates.

&FromDate, what do i do with this one? replace with one?

The cell which houses a start date criterion.

ValueRange is the values that i'm asking for it to sum

Yes.

and the other end is the To ranges...

That is the cell which houses an end date criterion.

Example:

=SUMIF(\$A\$2:\$A\$10,">="&E2,\$B\$2:\$B\$10)-SUMIF(\$A\$2:\$A\$10,">"&F2,\$B\$2:\$B\$10)

A2:A10 houses dates.
B2:B10 houses dollar values.

E2 houses the start date of a period of interest, F2 the end end of a period of interest.

thanks vor the help..

however, the formuale below assumes that dates is in A column (that is starrt and end date)...,

i've actually got start date column A, end date column B, and Values at column C,

how do i amend the below formula provided by aladin.

the comparison logic is that it must add values in colum C where start date and end date meets the financial year criteria as expalined earlier..

eg. 1/7/2002 to the 30/6/2003 is a financial year inclusive in Australia..

column A and B containt start and end dates....
thanks anybody?

further clarification

that is,

the start date of E2 in Aladin's example must be greater or equal to 1/7/2002

the end date at F2 in Aladin's example must be less than or equal to
30/6/2003

where the values in col A and col B meet this criteria, than add the corresond value to that row its testing, continue down the row until the end of the range of value sin Col A and B and sum.

Re: hi Aladin or somebody else

tankan said:
thanks vor the help..

however, the formuale below assumes that dates is in A column (that is starrt and end date)...,

i've actually got start date column A, end date column B, and Values at column C,

how do i amend the below formula provided by aladin.

the comparison logic is that it must add values in colum C where start date and end date meets the financial year criteria as expalined earlier..

eg. 1/7/2002 to the 30/6/2003 is a financial year inclusive in Australia..

column A and B containt start and end dates....
thanks anybody?

=SUMPRODUCT(--(\$A\$2:\$A\$10>=E2),--(\$B\$2:\$B\$100<=F2),\$C\$2:\$C\$100)

Replies
3
Views
105
Replies
9
Views
162
Replies
2
Views
61
Replies
19
Views
656
Replies
3
Views
245

1,203,752
Messages
6,057,151
Members
444,908
Latest member
Jayrey

### 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