How to return totals with dates between 52 weeks?

**chanpink** · 03-28-2013, 04:03 PM

I have dates ranging from 4/1/2012 to 3/31/2013. Each date has a total amount of customers. How can I separate my date ranges into weekly ranges with totals? In Column A1: Dates 4/1/2012 to 3/31/2013. In column B1: Number of Customers.

I want to be able to see the customer totals in a 52 week range vs. a program year range. Thank you.

**Melvinrobb** · 03-28-2013, 04:14 PM

Just want to double check, you have excel 2003?

This is MUCH easier in a newer version, but still possible if you have the 2003 version.

**chanpink** · 03-28-2013, 04:16 PM

My apologies, I have Excel 2010

**Pepe Le Mokko** · 03-28-2013, 04:18 PM

How do you define a week ? See http://www.cpearson.com/excel/WeekNumbers.aspx

**Melvinrobb** · 03-28-2013, 04:21 PM

I'm not sure using the WEEKNUM function will work great, simply because you aren't working with a calendar year.
I've attached a file with a couple solution, the first solution using a helper column, the second solution using the SUMIFS function based on the start and end date of the week.

**chanpink** · 03-28-2013, 04:59 PM

The 'define a week' link was very helpful.
In my data some dates are multiplied and are varied based on the customer's division.
Is it possible to segment by week (52 weeks) the amount of customers in each division?

For example, week 1 = each division with their amount of customers, and so on.

A file is attached with data to reference.
Thank you.