sumproduct between dates

[Dominique Feteau]

I have a sheet that has nothing but data. Columns are: 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?

 

[Biff]

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"),D220)

Biff

 

[Arvi Laanemets]

Hi

An example.
Sheet1: Data (in same order as in your message) in range A2100
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

 

[Arvi Laanemets]

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

 

[Bob Phillips]

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)

 

[Don]

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

I get #value

 

[Daniel.M]

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.

 

[Bob Phillips]

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)

 

[Dominique Feteau]

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