ExcelBanter - Summing By Week

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Summing By Week (https://www.excelbanter.com/excel-worksheet-functions/151063-summing-week.html)

I have daily data like so:

Date Volume
20070103 25
20070103 25
20070103 25
20070103 25
20070103 11
20070103 11
20070104 11
20070104 11
20070103 10
20070203 10
20070203 10

I am trying to find a way to sum the volume by week. Is this possible ?

Thank you in advance.

carl

=SUMPRODUCT(--(A2:A20=--"2007-01-03"),--(A2:A20<--"200-01-10"),B2:B20)

You could put the dates inb cells and test against the cells

=SUMPRODUCT(--(A2:A20=M1),--(A2:A20<M1+7),B2:B20)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"carl" wrote in message
...
I have daily data like so:

Date Volume
20070103 25
20070103 25
20070103 25
20070103 25
20070103 11
20070103 11
20070104 11
20070104 11
20070103 10
20070203 10
20070203 10

I am trying to find a way to sum the volume by week. Is this possible ?

Thank you in advance.

carl

Hi Bob. Thanks for your help. I should have been more specific. My data
covers year-to-date. So I have Jan, Feb,Mar,April, May,June, part of July
daily data.

I am trying to find a slick way to sum the Volume by week.

Any thoughts ?

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20=--"2007-01-03"),--(A2:A20<--"200-01-10"),B2:B20)

You could put the dates inb cells and test against the cells

=SUMPRODUCT(--(A2:A20=M1),--(A2:A20<M1+7),B2:B20)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"carl" wrote in message
...
I have daily data like so:

Date Volume
20070103 25
20070103 25
20070103 25
20070103 25
20070103 11
20070103 11
20070104 11
20070104 11
20070103 10
20070203 10
20070203 10

I am trying to find a way to sum the volume by week. Is this possible ?

Thank you in advance.

carl

I'm not sure about your dates, is it a custom date format in which case you
can use a helper column to find the week number. e.g =WEEKNUM(A2,1) gives you
a week starting on Sunday.

if these dates are text then to find the weeknumber use
=WEEKNUM(DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2)),1) and copy down.

To have a column of weekly totals fill in the series from 1 to 52 in say,
column C and use a simple SUMIF function e.g
=SUMIF($C$2:$C$500,D2,$B$2:$B$500)

Regards
Peter
"carl" wrote:

Hi Bob. Thanks for your help. I should have been more specific. My data
covers year-to-date. So I have Jan, Feb,Mar,April, May,June, part of July
daily data.

I am trying to find a slick way to sum the Volume by week.

Any thoughts ?

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20=--"2007-01-03"),--(A2:A20<--"200-01-10"),B2:B20)

You could put the dates inb cells and test against the cells

=SUMPRODUCT(--(A2:A20=M1),--(A2:A20<M1+7),B2:B20)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"carl" wrote in message
...
I have daily data like so:

Date Volume
20070103 25
20070103 25
20070103 25
20070103 25
20070103 11
20070103 11
20070104 11
20070104 11
20070103 10
20070203 10
20070203 10

I am trying to find a way to sum the volume by week. Is this possible ?

Thank you in advance.

carl