Calculation Between 2 Dates

[LF]

Hi, I am trying to find a formula how 2 calculate the sum between 2 Dates

I have a sheet contains a lot of information
Starting Date Qty Value
14/04/2009 10 1000
14/04/2009 15 2000
15/04/2009 2 1000
30/04/2009 1 1000

the sheet is long, & I need to create a summary report which shows the sum
by selecting the range according to the date

I have used the sumif but it doesn't work

please assist

thank you

 

[Sheeloo]

with the dates in E1 and E2 try
=SUMPRODUCT(--(A1:A100>E1),--(A1:A100<E2),C1:C100)
if you want to sum Col C between the dates E1 and E2

Adjust the range according to your data

 

[Francis]

Which are the dates you want to sum?

Assuming you want to sum up values with 14/04/2009
try this, your date in col A is assume to be real dates

=SUMIF(A2:A5,DATE(2009,4,14),C2:C5)

this will give you 3000
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Jacob Skaria]

Another way using SUMIF;

=SUMIF(A:A,">"&E1,C:C)-SUMIF(A:A,"<"&E2,C:C)

where E1 and E2 are query dates..

If this post helps click Yes

 

[Francis]

should it read as

=SUMIF(A:A,">"&E1,C:C)-SUMIF(A:A,">"&F1,C:C)
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Gord Dibben]

I just have to ask.................shouldn't "greek" be spelled "geek"?

Otherwise I don't see any sense to the sig line<g>


Gord Dibben MS Excel MVP

 

[LF]

Dear All,

the calculation is not correct as the table contains a lot of dates, the
excel sheet contains more than 350 lines, and the last formula you have
provided me get the Qty for the previous dates.

if the table contains March, they are included in the formula.

thank you

 

[Ron Rosenfeld]

Another way using SUMIF;

=SUMIF(A:A,">"&E1,C:C)-SUMIF(A:A,"<"&E2,C:C)

where E1 and E2 are query dates..

That won't work.

If E1 and E2 are, for example Mar 1 2009 and Mar 31 2009, and the desired range
is 1 Mar to 31 Mar inclusive, then the formula should read something like:

=sumif(a:a,">="&e1,c:c) - sumif(a:a,">"&e2,c:c)

--ron

 

[Shane Devenshire]

but geeks can't spell, or isn't that the point?


Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi,

The problem here is not the answers, it is the question. You should tell us
1. whether you are counting the number of items in the Qty or Value field 2.
Maybe you really want to sum the Qty or Value field?, 3. If there is data in
all the cells of the column you want to count (Qty or Value) then counting
the number of items between two date means you can ignore the Qty or Value -
so is there data in all the cells of the column you want to count?

================
Suppose the dates are in A2:A400 and the Qty in B2:B400 and the Values in
C2:C400, and suppose you want the count the number of items in the Qty field
that fall between 1/1/2009 and 1/2/2009 (Day/Month/Year). Then

=SUMPRODUCT(--(A2:A400>=--"1/1/09"),--(A2:A400<=--"1/2/09"),--(B2:B400<>""))

Now if all the cells in column B contain entries when there are entries in
column A, then you can simplify this formula down to

=SUMPRODUCT(--(A2:A400>=--"1/1/09"),--(A2:A400<=--"1/2/09"))

If you are really trying to sum the Qty field then the formula would be

=SUMPRODUCT(--(A2:A400>=--"1/1/09"),--(A2:A400<=--"1/2/09"),B2:B400)

Now you can simplify all these approach by entering the start data and the
end date in cell of the spreadsheet for example D1 and D2

Then the three formulas become:
=SUMPRODUCT(--(A2:A400>=D1),--(A2:A400<=D2),--(B2:B400<>""))
=SUMPRODUCT(--(A2:A400>=D1),--(A2:A400<=D2))
=SUMPRODUCT(--(A2:A400>=D1),--(A2:A400<=D2),B2:B400)

In 2007 you can use the above or

=COUNTIFS(A2:A400,">=1/1/09",A2:A400,"<=1/2/09",B2:B400,"<>")
=COUNTIFS(A2:A400,">=1/1/09",A2:A400,"<=1/2/09")
=SUMIFS(B2:B400,A2:A400,">=1/1/09",A2:A400,"<=1/2/09")

or using the cell references for the start and end dates
=COUNTIFS(A2:A400,">="&D1,A2:A400,"<="&D2,B2:B400,"<>")
=COUNTIFS(A2:A400,">="&D1,A2:A400,"<="&D2)
=SUMIFS(B2:B400,A2:A400,">="&D1,A2:A400,"<="&D2)

If you are trying to work with the Value column change the above references
to C2:C400 or whatever your range is.

 

[Jacob Skaria]

Thanks Francis; it should be

=SUMIF(A:A,">="&E1,C:C)-SUMIF(A:A,">"&E2,C:C)

when you have the query dates in E1 and E2..

If this post helps click Yes

 

[Bernard Liengme]

With the dates in A, Qty in B, Value in C
Start date in F1 and end date in G1
Use formula =SUMPRODUCT(--(A1:A4>=F10),--(A1:A4<=G1),B1:B4) to add all Qty
between the two dates (inclusive)
Note that only in XL2007 can you use full column references with SUMPRODUCT
=SUMPRODUCT(--(A:A>=F10),--(A:A<=G1),B:B
Or use longer formula
=SUMIF(A1:A4,">="&F1,B1:B4)-SUMIF(A1:A4,">"&G1,B1:B4)
or full column equivalent (in any version of Excel)

best wishes