sumproduct between dates
 

I have a sheet that has nothing but data. Columns a Date, Employee, Form,
Number

On another sheet, i want to add the total of "numbers" per "form" per week
from the "date"

now the date on the first sheet is daily. on the second sheet its "week
ending".

how do i set up the formula to add up only those for a certain week?

Hi!

If you have the week ending date all you need to do is
subtract 6 to get the week starting date.

On your sheet2 assume you have the week ending date in
cell A2. The formula would be something like this:

Assume:

Sheet1 Col A = dates
Sheet1 Col C = form
Sheet1 Col D = number


=SUMPRODUCT(--(Sheet1!A2:A20=A2-6),--(Sheet1!A2:A20<=A2),-
-(Sheet1!C2:C20="whatever"),D2:D20)

Biff

-----Original Message-----
I have a sheet that has nothing but data. Columns a
Date, Employee, Form,
Number

On another sheet, i want to add the total of "numbers"
per "form" per week
from the "date"

now the date on the first sheet is daily. on the second
sheet its "week
ending".

how do i set up the formula to add up only those for a
certain week?


.

Hi

An example.
Sheet1: Data (in same order as in your message) in range A2:D100
Sheet2:
A2=LastDateOfWeek (a date!)
B2=Form
The formula will be
=SUMPRODUCT(--(Sheet1!$A$2:$A$100<=A2),--(Sheet1!$A$2:$A$100<(A2-7)),--(Shee
t1!$C$2:$C$100=B2),Sheet1!$D$2:$D$100)

Arvi Laanemets


"Dominique Feteau" wrote in message
...
I have a sheet that has nothing but data. Columns a Date, Employee,
Form,
Number

On another sheet, i want to add the total of "numbers" per "form" per week
from the "date"

now the date on the first sheet is daily. on the second sheet its "week
ending".

how do i set up the formula to add up only those for a certain week?

Oops! A typo! The right formula is here

=SUMPRODUCT(--(Sheet1!$A$2:$A$100<=A2),--(Sheet1!$A$2:$A$100(A2-7)),--(Shee
t1!$C$2:$C$100=B2),Sheet1!$D$2:$D$100)


Arvi Laanemets

As an alternative, if you could use

=SUMPRODUCT(--(WEEKNUM(Sheet1!$A$2:$A$100)=WEEKNUM(A2)),Sheet1!$ D$2:$D$100)


--

HTH

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


"Dominique Feteau" wrote in message
...
I have a sheet that has nothing but data. Columns a Date, Employee,
Form,
Number

On another sheet, i want to add the total of "numbers" per "form" per week
from the "date"

now the date on the first sheet is daily. on the second sheet its "week
ending".

how do i set up the formula to add up only those for a certain week?

Hi Bob,
I like your formula but I cannot get it to work. Does it work for you?

I get #value

"Bob Phillips" wrote in message
...
As an alternative, if you could use

=SUMPRODUCT(--(WEEKNUM(Sheet1!$A$2:$A$100)=WEEKNUM(A2)),Sheet1!$ D$2:$D$100)


--

HTH

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


"Dominique Feteau" wrote in message
...
I have a sheet that has nothing but data. Columns a Date, Employee,
Form,
Number

On another sheet, i want to add the total of "numbers" per "form" per
week
from the "date"

now the date on the first sheet is daily. on the second sheet its "week
ending".

how do i set up the formula to add up only those for a certain week?

Hi,

It can't work.

WEEKNUM() can't return an Array : that's cleary a Microsoft's implementation
weakness.

But if you can live with a formula that's only compatible with a 1900 calendar:

=SUMPRODUCT((FLOOR(Sheet1!$A$2:$A$100,7)=FLOOR(A2, 7))* Sheet1!$D$2:$D$100)

If you want a (longer) 1900/1904 calendar compatible formula, advise here!

Regards,

Daniel M.


"Don" wrote in message
...
Hi Bob,
I like your formula but I cannot get it to work. Does it work for you?

I get #value

"Bob Phillips" wrote in message
...
As an alternative, if you could use

=SUMPRODUCT(--(WEEKNUM(Sheet1!$A$2:$A$100)=WEEKNUM(A2)),Sheet1!$ D$2:$D$100)


--

HTH

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


"Dominique Feteau" wrote in message
...
I have a sheet that has nothing but data. Columns a Date, Employee,
Form,
Number

On another sheet, i want to add the total of "numbers" per "form" per
week
from the "date"

now the date on the first sheet is daily. on the second sheet its "week
ending".

how do i set up the formula to add up only those for a certain week?

Sorry about that, too good to be true. As Daniel says, the fact that it
doesn't work is a flaw in the implementation of the WEENIE function.

Daniel's formula works, but whilst it has a more immediate test that Arvi
and Biff's solutions, it is not as intuitive. I tried (and failed :-)) to
provide a more elegant solution, which didn't work, so I would go with your
first solutions.

--

HTH

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


"Don" wrote in message
...
Hi Bob,
I like your formula but I cannot get it to work. Does it work for you?

I get #value

"Bob Phillips" wrote in message
...
As an alternative, if you could use


=SUMPRODUCT(--(WEEKNUM(Sheet1!$A$2:$A$100)=WEEKNUM(A2)),Sheet1!$ D$2:$D$100)


--

HTH

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


"Dominique Feteau" wrote in message
...
I have a sheet that has nothing but data. Columns a Date, Employee,
Form,
Number

On another sheet, i want to add the total of "numbers" per "form" per
week
from the "date"

now the date on the first sheet is daily. on the second sheet its
"week
ending".

how do i set up the formula to add up only those for a certain week?

Thanks guys, but I figured out a better way using pivottables. It wont
update automatically but i know of a way to do that.

lata
niq

"Dominique Feteau" wrote in message
...
I have a sheet that has nothing but data. Columns a Date, Employee,
Form, Number

On another sheet, i want to add the total of "numbers" per "form" per week
from the "date"

now the date on the first sheet is daily. on the second sheet its "week
ending".

how do i set up the formula to add up only those for a certain week?