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

with the dates in E1 and E2 try
=SUMPRODUCT(--(A1:A100E1),--(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

"LF" wrote:

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

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



"LF" wrote:

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

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
---------------
Jacob Skaria


"LF" wrote:

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

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



"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"LF" wrote:

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

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


On Sat, 30 May 2009 11:26:02 -0700, Francis wrote:

cheers, francis

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

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

--
LF


"Francis" wrote:

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



"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"LF" wrote:

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

On Sat, 30 May 2009 11:29:01 -0700, Jacob Skaria
wrote:

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

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


Cheers,
Shane Devenshire


"Gord Dibben" wrote:

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


On Sat, 30 May 2009 11:26:02 -0700, Francis wrote:

cheers, francis

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

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"LF" wrote:

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

--
LF


"Francis" wrote:

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



"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"LF" wrote:

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

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
---------------
Jacob Skaria


"LF" wrote:

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

--
LF


"Francis" wrote:

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



"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"LF" wrote:

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

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
--
Bernard Liengme
MVP Excel
http://people.stfx.ca

"LF" wrote in message
...
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
--
LF