SumIf( range, criteria, sum_range )

[andré C.]

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

 

[Joel]

It is better to use SUMPRODUCT

=SUMPRODUCT(--(A1:A100>=DATEVALUE("1/1/09")),--(A1:A100<DATEVALUE("3/1/09")),C1:C100)

 

[T. Valko]

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)

 

[Shane Devenshire]

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=H1)*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.