Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf( range, criteria, sum_range )
Hi,
i'm using 3 columns in excel 2007 "dates" - "code" - "money" I use sumIF to get the total of money for each code. This works great. But I would like to do so between two dates from column "dates". To sumIF the table between two dates... How could I do this ? Thanks, Andy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf( range, criteria, sum_range )
It is better to use SUMPRODUCT
=SUMPRODUCT(--(A1:A100=DATEVALUE("1/1/09")),--(A1:A100<DATEVALUE("3/1/09")),C1:C100) "andré C." wrote: Hi, i'm using 3 columns in excel 2007 "dates" - "code" - "money" I use sumIF to get the total of money for each code. This works great. But I would like to do so between two dates from column "dates". To sumIF the table between two dates... How could I do this ? Thanks, Andy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf( range, criteria, sum_range )
Use cells to hold your criteria:
E2 = lower date boundary F2 = upper date boundary G2 = some code Then: =SUMIFS(Money,Dates,"="&E2,Dates,"<="&F2,Codes,G2 ) -- Biff Microsoft Excel MVP "andré C." wrote: Hi, i'm using 3 columns in excel 2007 "dates" - "code" - "money" I use sumIF to get the total of money for each code. This works great. But I would like to do so between two dates from column "dates". To sumIF the table between two dates... How could I do this ? Thanks, Andy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIf( range, criteria, sum_range )
Hi,
I would also consider using reference cell with SUMPRODUCT =SUMPRODUCT((A1:A100=F1)*(A1:A100<=G1)*C1:C100) Where F1 contains the start date, G1 the end data column A the dates and column C the values. If you are also testing with Code at the same time then =SUMPRODUCT((A1:A100=F1)*(A1:A100<=G1)*(B1:B100=H 1)*C1:C100) Where H1 contains the code and B1:B100 the range with codes. A minor point - its not just better to use SUMPRODUCT rather than SUMIF in 2003 or earlier, you can't do it with SUMIF, so you must resort to another approach. In 2007 the new SUMIFS (and COUNTIFS, AVERAGEIF) functions allows multiple criteria. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "andré C." wrote: Hi, i'm using 3 columns in excel 2007 "dates" - "code" - "money" I use sumIF to get the total of money for each code. This works great. But I would like to do so between two dates from column "dates". To sumIF the table between two dates... How could I do this ? Thanks, Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif with multiple critera range and sum_range | Excel Worksheet Functions | |||
SUMIF - Sum_Range is misleading | Excel Worksheet Functions | |||
SUMIF with the Sum_range across several colums | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
SUMIF with multi-column sum_range | Excel Worksheet Functions |