I have a list of sales data. Column A is a list of dates (workdays). Column
B is the corresponding sales for those days.

I need to write a formula that will give me the sum of prior years sales
from the first of that year to the current date of that year. For instance,
today's date is 7/29/08. I want a formula that will sum the sales between
1/1/07 through 7/29/07. Any help would be appreciated.
--
Don Rountree

One way, in say, C1:
=SUMPRODUCT((A2:A100=DATE(YEAR(TODAY())-1,1,1))*(A2:A100<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))),B2:B100)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---
"Don" wrote:
I have a list of sales data. Column A is a list of dates (workdays). Column
B is the corresponding sales for those days.

I need to write a formula that will give me the sum of prior years sales
from the first of that year to the current date of that year. For instance,
today's date is 7/29/08. I want a formula that will sum the sales between
1/1/07 through 7/29/07. Any help would be appreciated.
--
Don Rountree

Try this:

=SUMPRODUCT((A1:A100=DATE(YEAR(TODAY())-1,1,1))*(A1:A100<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*B1:B100)

Hope this helps.

Pete

On Jul 29, 1:46*pm, Don wrote:
I have a list of sales data. *Column A is a list of dates (workdays). *Column
B is the corresponding sales for those days.

I need to write a formula that will give me the sum of prior years sales
from the first of that year to the current date of that year. *For instance,
today's date is 7/29/08. *I want a formula that will sum the sales between
1/1/07 through 7/29/07. *Any help would be appreciated.
--
Don Rountree

Thank you.
--
Don Rountree


"Pete_UK" wrote:

Try this:

=SUMPRODUCT((A1:A100=DATE(YEAR(TODAY())-1,1,1))*(A1:A100<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*B1:B100)

Hope this helps.

Pete

On Jul 29, 1:46 pm, Don wrote:
I have a list of sales data. Column A is a list of dates (workdays). Column
B is the corresponding sales for those days.

I need to write a formula that will give me the sum of prior years sales
from the first of that year to the current date of that year. For instance,
today's date is 7/29/08. I want a formula that will sum the sales between
1/1/07 through 7/29/07. Any help would be appreciated.
--
Don Rountree

Thank you.
--
Don Rountree


"Max" wrote:

One way, in say, C1:
=SUMPRODUCT((A2:A100=DATE(YEAR(TODAY())-1,1,1))*(A2:A100<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))),B2:B100)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---
"Don" wrote:
I have a list of sales data. Column A is a list of dates (workdays). Column
B is the corresponding sales for those days.

I need to write a formula that will give me the sum of prior years sales
from the first of that year to the current date of that year. For instance,
today's date is 7/29/08. I want a formula that will sum the sales between
1/1/07 through 7/29/07. Any help would be appreciated.
--
Don Rountree

Welcome, Don. Pl press the Yes button (like the one below from where you're
reading this) in both responses which helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---
"Don" wrote:
Thank you.
--
Don Rountree

You're welcome, Don. I see that Max and I came up wth virtually the
same solution.

Pete

On Jul 29, 2:28*pm, Don wrote:
Thank you.
--
Don Rountree