Sum daily sales by week

[Harry Flashman]

In column A i have range of cells with dates. For example $A$2:$A$1000
that I have named "SalesDate"
In Column B I have range of cells with a dollar values. For example $B
$1000 that I have named "SalesValue"

On another worksheet I have successfully summed the monthly sales
figures using the following formula:
=SUMPRODUCT((MONTH(SalesDate)=MONTH(A2))*(SalesValue))

In the above example B2 = Jan-01

This variation also worked:
=SUMPRODUCT(((DATE(YEAR(SalesDate),MONTH(SalesDate),
1)=A2))*(SalesValue))

Now I would like to sum the weekly sales. I tried to use this formula
but without succcess:
=SUMPRODUCT((WEEKNUM(SalesDate)=WEEKNUM(A2))*(SalesValue))

This returns #VALUE!

I have search the internet for a solution but without success. I
gather that WEEKNUM does not work with arrays.

I solved my problem a helper column next to my date range which uses
WEEKNUM and then I have referenced the WEEKNUM range with the SUMIF
function. This works, so in a sense I have solved my problem.

But I am curious. Can this be solved without a helper column?

One of the reasons I ask is that I am trying to help my girlfriend out
and her company uses a spreadsheet which is formatted a particular way
and she would prefer it if I did not alter it by adding another column
(silly I know).

However it would surprise me if there wasn't some easy way of doing
this. One post I read suggested using UDF version of WEEKNUM that does
allow arrays, but I couldn't get it to work.

Any ideas? or is what I am asking not practical?

 

[Harry Flashman]

Slight error above:
I meant A2 = Jan-01 (not B2 = Jan-01)

 

[Roger Govier]

Hi

Try
=SUMPRODUCT((TEXT(salesdate,"ddmmyy")>=TEXT(A2,"ddmmyy"))
*(TEXT(salesdate,"ddmmyy")<=TEXT(A2+6,"ddmmyy"))*Salesvalue)

 

[Harry Flashman]

Hi

Try
=SUMPRODUCT((TEXT(salesdate,"ddmmyy")>=TEXT(A2,"ddmmyy"))
*(TEXT(salesdate,"ddmmyy")<=TEXT(A2+6,"ddmmyy"))*Salesvalue)

--
Regards
Roger Govier

















- Show quoted text -

Hi Roger,

Thanks for looking into that for me. Unfortunately it didn't quite
work for me.

A B
Date Amount Week
01/01/08 100 1
10/01/08 100 2
16/01/08 100 3
26/01/08 100 4
01/02/08 100 5

The cells in column A I named "SalesDate" and the cells in Column B I
named "SalesValue".

On my next sheet I used your formula:
=SUMPRODUCT((TEXT(SalesDate,"ddmmyy")>=TEXT(A2,"ddmmyy"))*(TEXT(SalesDate,"ddmmyy")<=TEXT(A2+6,"ddmmyy"))*SalesValue)

The A column consisted of the week number and B column is what you
formula returned:
Week Value
1 200
2 0
3 0
4 0
5 100

So it got week 1 and week 5 correct but not weeks 2 to 4.

As I mentioned the problem can be solved with a helper column next to
my dates and then referencing that column with SUMIF.
I'll keep playing around, though, and see if I can tweak your formula.
It surprises me that WEEKNUM does not work with arrays. I would have
thought that this would be something that lots of people would do.

Thanks again for you help.

Harry