get summary of cell range where all cells fullfil a certain vlookup request

  • Thread starter Thread starter Norbert Jaeger
  • Start date Start date
N

Norbert Jaeger

Hello,

in column A (A5:A370) are dates from 01-01-2005 to 31-12-2005
in column B (B5:B370) are the week no. for each date, produced by
following formula:
=ROUNDDOWN(((A7-WEEKDAY(A7,2)-DATE(YEAR(A7+4-WEEKDAY(A7,2)),1,-10))/7),0)
where A7 is the 03-01-2005 and the result is 1, for week no.1

in column C (C5:C370) are production figures

How can I get the sum of production figures of a certain week.

Many thanks for any suggestion

Regards,
Norbert
 
Hi,
Why reinvent the wheel? Why not use:
=WEEKNUM(A5)

This will sum column C if the week is 8:
=SUMIF(B5:B369,8,C5:C369)

Don Psitulka
 
is this the answer your looking for or have i misread the question:

=SUMIF(B:B,e1,C:C)

Where e1 is a reference to a cell containing the week you want or th
week is actually in the formula:

=SUMIF(B:B,"1",C:C)

?
 
Thanks very much Don and John!

I didn't think it was so easy. Sorry for that.
I am a bit out of training, but I think your suggestion Don, using
weeknum() doesn't work with my Excel 2000.

Maybe only in a later version.

Thanks anyway.
Norbert
 
Norbert
If this function is not available, run the Setup program to install the
Analysis ToolPak. After you install the Analysis ToolPak, you must enable it
by using the Add-Ins command on the Tools menu.

Don Psitulka
 
Back
Top