sumproduct help

[ryan.fitzpatrick3]

This probably is an easy answer I hope. I have 2 columes: col A has
dates lets say 11-1-08 to 11-10-08 and col B has numbers, any number.
Now if I want to do sum up col B by only pulling the last 5 dates in
col A, 11-6-08 to 11-10-08 how would I do this.

I have something like:
S3 in this case is how many days I want to go back, 5 days in this
case. so S3 has 5 in cell.

=SUMPRODUCT(($A$3:$A$10>$S3),B$3:$B$10)

I totally sure this is incorrect but I know i'm at least on the right
track, because I've used sumproduct before but only matching cells in
ranges and not summing up greater or less than a certain date. Does
this make sense?

Ryan

 

[Lars-Åke Aspelin]

This probably is an easy answer I hope. I have 2 columes: col A has
dates lets say 11-1-08 to 11-10-08 and col B has numbers, any number.
Now if I want to do sum up col B by only pulling the last 5 dates in
col A, 11-6-08 to 11-10-08 how would I do this.

I have something like:
S3 in this case is how many days I want to go back, 5 days in this
case. so S3 has 5 in cell.

=SUMPRODUCT(($A$3:$A$10>$S3),B$3:$B$10)

I totally sure this is incorrect but I know i'm at least on the right
track, because I've used sumproduct before but only matching cells in
ranges and not summing up greater or less than a certain date. Does
this make sense?

Ryan

Try this formula:

=SUMPRODUCT(- -(A1:A10>=LARGE(A1:A10,S3)),B1:B10)

Change the 10 to be at least as large as the number of data you have.

Hope this helps / Lars-Åke

 

[ryan.fitzpatrick3]

That didn't work. Doesn't large bring back the 5 largest numbers if I
put 5 in there, I want a sum of all of those numbers greater than the
date in the cell. Make sense?

 

[Lars-Åke Aspelin]

Which data did you test with, what result did you expect, and what
result did you get?

/ Lars-Åke

 

[Lars-Åke Aspelin]

2008-11-01 1
2008-11-02 2
2008-11-03 3
2008-11-04 4
2008-11-05 5
2008-11-06 6
2008-11-07 7
2008-11-08 8
2008-11-09 9
2008-11-10 10

With these data in columns A and B and the number 5 in cell S3 I get
the result 40 which exactly what I expect. (6+7+8+9+10)

Do you also get 40? Do you expect something else, what?

/ Lars-Åke

 

[Rick Rothstein]

Does this do what you want...

=SUMPRODUCT((A1:A100>=LARGE(A1:A100,S3))*B1:B100)

Change the row number of the upper part of each range (the 100 in my
example) to the highest row number you expect to have data in.

 

[ryan.fitzpatrick3]

I got this to work, now instead of summing the total where do I put in
average?

 

[ryan.fitzpatrick3]

Nevermind, I figured it out. I just did this and it worked.

=SUMPRODUCT((A1:A100>=LARGE(A1:A100,S3))*B1:B100)/S3

Thanks everyone for helping, sorry for any confusion/frustration.

Ryan

 

[ryguy7272]

This is a great resource:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards,
Ryan---

 

[ryan.fitzpatrick3]

yes this is where I first learned the dynamics of sumproduct, but I
forgot about this website. Thanks.