Summing by Date Range

[Ken Zenachon]

I have a spreadsheet containing purchases made at various suppliers.
Columns are Supplier, Invoice Date and Amount. It's easy enough to use
SUMIF and return a figure for how much I spent at each supplier, but I
can't figure out to calculate how much I spent in a given span of time,
or better yet, how much I spent in a given timespan at a given
supplier. Right now my primary concern is calculating how much I spent
in each tax year, say, from Jan 1, 1998 to Dec 31, 1998 and so on.

It must be simple but I'm breaking my head on it. Any takers?

KZ

 

[Biff]

Hi!

To sum for a specific time-span:

D1 = 1/1/1998
D2 = 12/31/1998
D3 = Supplier

=SUMIF(B1:B100,">="&D1,C1:C100)-SUMIF(B1:B100,">"&D2,C1:C100)

To sum for a specific time-span for a specific supplier:

=SUMPRODUCT(--(A1:A100=D3),--(B1:B100>=D1),--(B1:B100<=D2),C1:C100)

Biff

 

[Ken Zenachon]

Hi, Biff,
I have two questions:

1. Yours is an elegant solution but is there no way to include the date
span in the formula itself?

2. Why, in the formula is the "greater than or equal to" enclosed in
quotation marks?

KZ

 

[Biff]

Hi!

1. Yours is an elegant solution but is there no way to include the date
span in the formula itself?

Yes, there are several ways to do that. For the SUMIF formula:

=SUMIF(B1:B10,">=1/1/1998",C1:C10)-SUMIF(B1:B10,">12/31/1998",C1:C10)

=SUMIF(B1:B10,">="&DATE(1998,1,1),C1:C10)-SUMIF(B1:B10,">"&DATE(1998,12,31),C1:C10)

=SUMIF(B1:B10,">="&DATEVALUE("1/1/1998"),C1:C10)-SUMIF(B1:B10,">"&DATEVALUE("12/31/1998"),C1:C10)

2. Why, in the formula is the "greater than or equal to" enclosed in
quotation marks?

I don't know the technical reason. Only MS knows for sure!

For the SUMPRODUCT formula:

=SUMPRODUCT(--(A1:A10="Supplier"),--(B1:B10>=--"1/1/1998"),--(B1:B10<=--"12/31/1998"),C1:C10)

=SUMPRODUCT(--(A1:A10="Supplier"),--(B1:B10>=DATE(1998,1,1)),--(B1:B10<=DATE(1998,12,31)),C1:C10)

=SUMPRODUCT(--(A1:A10="Supplier"),--(B1:B10>=DATEVALUE("1/1/1998")),--(B1:B10<=DATEVALUE("12/31/1998")),C1:C10)

As you can see, there are many options but consider this:

If you want to calculate for a different time-span then you have to edit the
formula itself.

Biff

 

[Ken Zenachon]

Biff, thanks for your help.
I played around with the options and it turns out your first suggestion
works best for me. Go figure.


Llike you said, to change the date range I don't have to touch the
formula, just a standalone date. Easy!

Thanks again!


KZ