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

[Guest]

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,

 

[Aladin Akyurek]

=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)

 

[Max]

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)

 

[Biff]

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

 

[Biff]

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

 

[Bob Phillips]

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

etc.

--

HTH

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

 

[Ron Rosenfeld]

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,

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