date formulae question help!!

[tankan]

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

 

[Aladin Akyurek]

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)

 

[tankan]

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

 

[Aladin Akyurek]

Re: 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..

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.

 

[tankan]

hi Aladin or somebody else

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?

 

[tankan]

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.

 

[Aladin Akyurek]

Re: hi Aladin or somebody else

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)