lookup and sum

[sd8]

"Sheet1"
a b c d
date parts surplus total
2011/04/10 $2500000 $150000 $2650000
2011/04/11 $2000000 $150000 $2150000
2011/04/12 etc
2011/04/13 etc
2011/04/14 etc
2011/04/15 etc
2011/04/16 etc
...

"Sheet1"
f g h i
date parts surplus total
2011/04/16 $XXX $XXX $XXX
2011/04/23 etc

in column "a" i have dates listed daily with corresponding values for parts, surplus, and total inventory values. I am trying to find a way to have column "f" (or a new sheet) sum the daily value into a weekending value (ie. "f" date is weekending and lookup "a" and sum that weeks values for each column). weeks are sunday to saturday (ex. 04/10 - 04/16).

a lookup and summary of the week in a new table... if that makes sense.

thanks

 

[SteveO59L]

Have a look at Pivot Tables

 

[T. Valko]

"Sheet1"
a b c d
date parts surplus total
2011/04/10 $2500000 $150000 $2650000
2011/04/11 $2000000 $150000 $2150000
2011/04/12 etc
2011/04/13 etc
2011/04/14 etc
2011/04/15 etc
2011/04/16 etc
...

"Sheet1"
f g h i
date parts surplus total
2011/04/16 $XXX $XXX $XXX
2011/04/23 etc

in column "a" i have dates listed daily with corresponding values for parts, surplus, and total inventory values. I am trying to find a way to have column "f" (or a new sheet) sum the daily value into a weekending value (ie. "f" date is weekending and lookup "a" and sum that weeks values for each column). weeks are sunday to saturday (ex. 04/10 - 04/16).

a lookup and summary of the week in a new table... if that makes sense.

thanks

What version of Excel are you using?

 

[sd8]

excel 2010

 

[T. Valko]

excel 2010

Ok, if you have the weekending dates listed starting in F2...

Enter this formula in G2:

=SUMIFS(B$2:B$8,$A$2:$A$8,">="&$F2-6,$A$2:$A$8,"<="&$F2)

Copy across to I2 then down as needed.

 

[sd8]

excellent! thank you.

 

[T. Valko]

excellent! thank you.

You're welcome. Thanks for the feedback!