how to sum using dates as a criteria

[Lius]

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

 

[Frank Kabel]

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

 

[JE McGimpsey]

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

 

[D@annyBoy]

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)

 

[D@annyBoy]

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),

 

[Frank Kabel]

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

 

[D@annyBoy]

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

 

[D@annyBoy]

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)

 

[D@annyBoy]

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)