how to sum using dates as a criteria
 

How do I add up a range of cells using dates as a criteria?

Hi
for example
=SUMIF(A1:A100,"=" & DATE(2004,10,1),B1:B100)

"Lius" wrote:

How do I add up a range of cells using dates as a criteria?

One way:

Assume your data are in columns A (Dates) and B (Values), and that your
criteria dates are in D1 and D2 (inclusive):

=SUMIF(A:A,"=" & D1, B:B) - SUMIF(A:A, "" & D2, B:B)

or

=SUMPRODUCT(--(A1:A1000=D1),--(A1:A1000<=D2),B1:B1000)

(See

http://www.mcgimpsey.com/excel/doubleneg.html

for an explanation of the "--").


In article ,
"Lius" wrote:

How do I add up a range of cells using dates as a criteria?

thanks it works

but can I use a range of dates instead

sum all cells if month is Jan
will this work =SUMIF(A1:A100,"=" & DATE(1,1,2004),"<="&
DATE1,2,2004,B1:B100)


"Frank Kabel" wrote in message
...
Hi
for example
=SUMIF(A1:A100,"=" & DATE(2004,10,1),B1:B100)

"Lius" wrote:

How do I add up a range of cells using dates as a criteria?

I have 2 columns of data
Under A is the dates
Under G is the values

I am trying to add up all the values if the month is Jan
"=" & DATE(1,1,2004), "<=" & DATE(31,1,2004),




"JE McGimpsey" wrote in message
...
One way:

Assume your data are in columns A (Dates) and B (Values), and that your
criteria dates are in D1 and D2 (inclusive):

=SUMIF(A:A,"=" & D1, B:B) - SUMIF(A:A, "" & D2, B:B)

or

=SUMPRODUCT(--(A1:A1000=D1),--(A1:A1000<=D2),B1:B1000)

(See

http://www.mcgimpsey.com/excel/doubleneg.html

for an explanation of the "--").


In article ,
"Lius" wrote:

How do I add up a range of cells using dates as a criteria?

Hi
try:
=SUMIF(A1:A100,"=" & DATE(2004,1,1))-SUMIF(A1:A100,"=" & DATE(2004,2,1))


"D@annyBoy" wrote:

thanks it works

but can I use a range of dates instead

sum all cells if month is Jan
will this work =SUMIF(A1:A100,"=" & DATE(1,1,2004),"<="&
DATE1,2,2004,B1:B100)


"Frank Kabel" wrote in message
...
Hi
for example
=SUMIF(A1:A100,"=" & DATE(2004,10,1),B1:B100)

"Lius" wrote:

How do I add up a range of cells using dates as a criteria?

no go

This is my data value

DATE LC NO LC AMOUNT
01-01-04 USD1,000.00
15-01-04 USD1,000.00
02-02-04 USD1,000.00


Try to create another sheet as follows;

CUSTOMER JAN FEB MAR
C.B.N. ONE OF THE BOYS 2,000.00 1,000.00


This is how is looks at the moment :-(

CUSTOMER JAN FEB MAR
C.B.N. ONE OF THE BOYS 3,000.00


Is this possible

"Frank Kabel" wrote in message
...
Hi
try:
=SUMIF(A1:A100,"=" & DATE(2004,1,1))-SUMIF(A1:A100,"=" & DATE(2004,2,1))


"D@annyBoy" wrote:

thanks it works

but can I use a range of dates instead

sum all cells if month is Jan
will this work =SUMIF(A1:A100,"=" & DATE(1,1,2004),"<="&
DATE1,2,2004,B1:B100)


"Frank Kabel" wrote in message
...
Hi
for example
=SUMIF(A1:A100,"=" & DATE(2004,10,1),B1:B100)

"Lius" wrote:

How do I add up a range of cells using dates as a criteria?

this is the formulae that I am using

=SUMIF(CBN!$A:$A,"=" & DATE(1,1,2004),CBN!$F:$F)
but it includes dates before 2003 as well

can you help newbie incorporate your suggest

I tried this, but it doesn't works

=SUMIF(CBN!$A:$A,"=" & DATE(2004,1,1)-SUMIF(CBN!$A:$A,"<=" &
DATE(2004,2,1)),CBN!$F:$F)



"Frank Kabel" wrote in message
...
Hi
try:
=SUMIF(A1:A100,"=" & DATE(2004,1,1))-SUMIF(A1:A100,"=" & DATE(2004,2,1))


"D@annyBoy" wrote:

thanks it works

but can I use a range of dates instead

sum all cells if month is Jan
will this work =SUMIF(A1:A100,"=" & DATE(1,1,2004),"<="&
DATE1,2,2004,B1:B100)


"Frank Kabel" wrote in message
...
Hi
for example
=SUMIF(A1:A100,"=" & DATE(2004,10,1),B1:B100)

"Lius" wrote:

How do I add up a range of cells using dates as a criteria?

thanks

I finally figure it out
For Jan I use
=SUMIF(CBN!$A:$A,"=" & DATE(2004,1,1),CBN!$F:$F)-SUMIF(CBN!$A:$A,"=" &
DATE(2004,1,31),CBN!$F:$F)
For Feb I use
=SUMIF(CBN!$A:$A,"=" & DATE(2004,2,1),CBN!$F:$F)-SUMIF(CBN!$A:$A,"=" &
DATE(2004,2,31),CBN!$F:$F)

"Frank Kabel" wrote in message
...
Hi
try:
=SUMIF(A1:A100,"=" & DATE(2004,1,1))-SUMIF(A1:A100,"=" & DATE(2004,2,1))


"D@annyBoy" wrote:

thanks it works

but can I use a range of dates instead

sum all cells if month is Jan
will this work =SUMIF(A1:A100,"=" & DATE(1,1,2004),"<="&
DATE1,2,2004,B1:B100)


"Frank Kabel" wrote in message
...
Hi
for example
=SUMIF(A1:A100,"=" & DATE(2004,10,1),B1:B100)

"Lius" wrote:

How do I add up a range of cells using dates as a criteria?