Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF for date
I would like to add the number from a range of date e.g. may 15, 2006 to
June 17, 2006 The data arrange in this way. ... ... .... ...... 14-5-2006 19 15-5-2006 12 16-5-2006 18 ... ... ... .. ... .. ... .. 17-6-2006 7 18-6-2006 9 ..... .. .... .. ... .. .... .. I want to get a number adding all B cells if the corresponding A cell is within the range. I used this formula. =SUMIF(A1:A10, "=DATEVALUE("15/05/06")", B1:B10) - SUMIF(A1:A10, "<DATEVALUE("18/06/06")", B1:B10) but it does not work Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF for date
Try removing the quotation marks around the DATEVALUE function:
=SUMIF(A1:A10,=DATEVALUE(15/05/06),B1:B10) - SUMIF(A1:A10,DATEVALUE(18/06/06),B1:B10) You may also want to experiment with converting the dates to serial numbers by formatting them as numbers, not dates. If you were to do that then you could get rid of the DATEVALUE function entirely and just SUMIF based on numerical values. Dave -- Brevity is the soul of wit. "Lamb Chop" wrote: I would like to add the number from a range of date e.g. may 15, 2006 to June 17, 2006 The data arrange in this way. ... ... .... ...... 14-5-2006 19 15-5-2006 12 16-5-2006 18 ... ... ... .. ... .. ... .. 17-6-2006 7 18-6-2006 9 ..... .. .... .. ... .. .... .. I want to get a number adding all B cells if the corresponding A cell is within the range. I used this formula. =SUMIF(A1:A10, "=DATEVALUE("15/05/06")", B1:B10) - SUMIF(A1:A10, "<DATEVALUE("18/06/06")", B1:B10) but it does not work Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF for date
I think I'd use:
=SUMIF(A1:A10,"="&date(2006,5,15), B1:B10) - SUMIF(A1:A10,"<"&DATE(2006,6,18), B1:B10) But you could try: =SUMIF(A1:A10, "="&DATEVALUE("15/05/06"), B1:B10) - SUMIF(A1:A10,"<"&DATEVALUE("18/06/06"), B1:B10) But using =datevalue() like this scares me. For me with my USA (mdy) settings, =datevalue("15/05/06") won't work. And rather than rely on an ambiguous date (=datevalue("03/04/05")), why not just remove all doubt. Lamb Chop wrote: I would like to add the number from a range of date e.g. may 15, 2006 to June 17, 2006 The data arrange in this way. .. .. ... ..... 14-5-2006 19 15-5-2006 12 16-5-2006 18 .. ... .. .. .. .. .. .. 17-6-2006 7 18-6-2006 9 .... .. ... .. .. .. ... .. I want to get a number adding all B cells if the corresponding A cell is within the range. I used this formula. =SUMIF(A1:A10, "=DATEVALUE("15/05/06")", B1:B10) - SUMIF(A1:A10, "<DATEVALUE("18/06/06")", B1:B10) but it does not work Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Help with SUMIF function | Excel Worksheet Functions | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions |