Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how to sum using dates as a criteria
How do I add up a range of cells using dates as a criteria?
|
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel | |||
Entering dates in Excel 2002 | Excel Discussion (Misc queries) | |||
I get wrong dates when i paste from a different sheet into a new s | Excel Discussion (Misc queries) | |||
Filtering with dates | Excel Discussion (Misc queries) |