Summing weekending dates

[Jim]

Hello,

Below is a sheet that shows where I store data.

20's 30's 40's 50's 60's
December 01, 2005
December 02, 2005
December 03, 2005 9 2 4 5
December 04, 2005
December 05, 2005 4 5 8 6
December 06, 2005
December 07, 2005
December 08, 2005
December 09, 2005 7 5 9 2 1

On a separate sheet I am trying to figure out a formula that will add seven
days of data from a weekending date.

For example if week ending is listed in A1 as Dec 9, 2005. How would the
formula look if I wanted to count all data from 12/3 – 12/9 from any of the
columns above (say for example from the 20’s column). Also – will you
include anything that would prevent an error if one of the cells was blank.

 

[Biff]

Hi!

Try this:

Assume your dates start in A2. 20's is in column B, 30's is in column C,
etc.

A1 = 12/9/2005

=SUMPRODUCT(--(A2:A13>=A1-6),--(A2:A13<=A1),--(B2:B13<>""))

Biff