How do i sum vales of sales falling between certain time periods
 

in column A is list of dates populated each day of the year from jan 1st to
dec 31st. And In column b i have the sales $ for that day. there is more than
1 sales entry for each day, hence multiple entries using the same date.

How do i have a summary table that shows only total sales between certain
time periods. eg march, or may

Eg, only sales from Column B, matching the date in colum A between 1-mar-05
and 31-mar05.

I was thinking: TO SUM SALES VALUES FROM COLUMN H1:H365 ONLY IF(A1:A365,
= 1 MAR 05 AND <=31 MAR 05

=SUMIF(DateRange,"="&X2,SumRange)-SUMIF(DateRange,""&Y2,SumRange)

where X2 is a true date like 1-Mar-05 and Y2 is either 31-Mar-05 or
computed from X2 with:

=DATE(YEAR(X2),MONTH(X2)+1,0)

the_kane wrote:
in column A is list of dates populated each day of the year from jan 1st to
dec 31st. And In column b i have the sales $ for that day. there is more than
1 sales entry for each day, hence multiple entries using the same date.

How do i have a summary table that shows only total sales between certain
time periods. eg march, or may

Eg, only sales from Column B, matching the date in colum A between 1-mar-05
and 31-mar05.

I was thinking: TO SUM SALES VALUES FROM COLUMN H1:H365 ONLY IF(A1:A365,

= 1 MAR 05 AND <=31 MAR 05

I was thinking: TO SUM SALES VALUES FROM COLUMN
H1:H365 ONLY IF(A1:A365, = 1 MAR 05
AND <=31 MAR 05

Assuming dates are in col A,

Try: =SUMPRODUCT(--(MONTH(A1:A365)=3),H1:H365)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"the_kane" wrote in message
...
in column A is list of dates populated each day of the year from jan 1st
to
dec 31st. And In column b i have the sales $ for that day. there is more
than
1 sales entry for each day, hence multiple entries using the same date.

How do i have a summary table that shows only total sales between certain
time periods. eg march, or may

Eg, only sales from Column B, matching the date in colum A between
1-mar-05
and 31-mar05.

I was thinking: TO SUM SALES VALUES FROM COLUMN H1:H365 ONLY IF(A1:A365,
= 1 MAR 05 AND <=31 MAR 05

Hi!

Try one of these:

=SUMPRODUCT(--(A1:A15=DATE(2005,3,1)),--(A1:A15<=DATE
(2005,3,31)),B1:B15)

Or, use cells to hold your date ranges:

D1 = 3/1/2205
E1 = 3/31/2005

=SUMPRODUCT(--(A1:A15=D1),--(A1:A15<=E1),B1:B15)

Biff

-----Original Message-----
in column A is list of dates populated each day of the
year from jan 1st to
dec 31st. And In column b i have the sales $ for that
day. there is more than
1 sales entry for each day, hence multiple entries using
the same date.

How do i have a summary table that shows only total sales
between certain
time periods. eg march, or may

Eg, only sales from Column B, matching the date in colum
A between 1-mar-05
and 31-mar05.

I was thinking: TO SUM SALES VALUES FROM COLUMN H1:H365
ONLY IF(A1:A365,
= 1 MAR 05 AND <=31 MAR 05
.

Hi Aladin!

I was wondering if you could take a look at a solution I
came up with to a post in the MISC group.

The subject line is: Reverse MATCH Function
Posted on March 8 2005 2:29 PM

It is very similar to that post from a couple of days ago
in which I used a SMALL array formula.

I would like to see what you would do with that post.

Biff

-----Original Message-----
=SUMIF(DateRange,"="&X2,SumRange)-SUMIF
(DateRange,""&Y2,SumRange)

where X2 is a true date like 1-Mar-05 and Y2 is either 31-
Mar-05 or
computed from X2 with:

=DATE(YEAR(X2),MONTH(X2)+1,0)

the_kane wrote:
in column A is list of dates populated each day of the
year from jan 1st to
dec 31st. And In column b i have the sales $ for that
day. there is more than
1 sales entry for each day, hence multiple entries
using the same date.

How do i have a summary table that shows only total
sales between certain
time periods. eg march, or may

Eg, only sales from Column B, matching the date in
colum A between 1-mar-05
and 31-mar05.

I was thinking: TO SUM SALES VALUES FROM COLUMN
H1:H365 ONLY IF(A1:A365,

= 1 MAR 05 AND <=31 MAR 05
.

=SUMPRODUCT(--(A1:A200=--"2005-03-01"),--(A1:A200<="2005-03-31")

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"the_kane" wrote in message
...
in column A is list of dates populated each day of the year from jan 1st
to
dec 31st. And In column b i have the sales $ for that day. there is more
than
1 sales entry for each day, hence multiple entries using the same date.

How do i have a summary table that shows only total sales between certain
time periods. eg march, or may

Eg, only sales from Column B, matching the date in colum A between
1-mar-05
and 31-mar05.

I was thinking: TO SUM SALES VALUES FROM COLUMN H1:H365 ONLY IF(A1:A365,
= 1 MAR 05 AND <=31 MAR 05

On Tue, 8 Mar 2005 21:39:04 -0800, "the_kane"
wrote:

in column A is list of dates populated each day of the year from jan 1st to
dec 31st. And In column b i have the sales $ for that day. there is more than
1 sales entry for each day, hence multiple entries using the same date.

How do i have a summary table that shows only total sales between certain
time periods. eg march, or may

Eg, only sales from Column B, matching the date in colum A between 1-mar-05
and 31-mar05.

I was thinking: TO SUM SALES VALUES FROM COLUMN H1:H365 ONLY IF(A1:A365,
= 1 MAR 05 AND <=31 MAR 05

In addition to the posted solutions, you could also investigate the use of a
Pivot Table. You would be able to group the sales by months.


--ron