Sum if between two dates

[Guest]

I have a spread sheet that in row e7 through r7 is a date that represnets the
week of. Example exapmle e7=7/3, f7=7/10, g7=7/17. E8 through r25
contains numbers which are number of pieces forecasted to be ordered. What I
would like to be able to do is type a date like 7/3 in d4 and 7/31 in e4 and
have excel look at the dates in row 7 and if greater than or equal lower date
and less than or equal to second date sum numbers in the corresponding
columns.

 

[Guest]

Try:

=SUMPRODUCT((E7:R7>=E4)*(E7:R7<=E5)*(E8:R8))

HTH

 

[Guest]

.... sorry should be ....

=SUMPRODUCT((E7:R7>=D4)*(E7:R7<=E4)*(E8:R8))

 

[Guest]

Thanks but that did not work. Spread sheet looks something like


E F G
7 7/3 7/10 7/17
8 100 150 200
9 200 101 252
10 125 333 222

D4 would contain 7/3 and E4 contain 7/17. I would like for Excel to look
at dates in D and E 4 and sum numbers under the columns that are between the
dates in D and E. Total should be something like 1683. Once again Thanks
for your help.

 

[Peo Sjoblom]

When you say didn't work what do you mean, computer exploded or what? It
always help the person who tries to help if the OP explains what does not
work, given your example if that is what it is based on I find it hard to
get 1683 since the total of all values in your example is 1543. What part of
the values in your example would you want to be included in the total if the
between dates are 7/3/06 and 7/17/06 and by between what do you mean,
including 7/3/06 and 7/17/06 or excluding those dates If those should be
included use

=SUMPRODUCT((E7:G7>=D4)*(E7:G7<=E4)*(E8:G10))

which returns 1543 thus all values are included or

=SUMPRODUCT((E7:G7>D4)*(E7:G7<E4)*(E8:G10))

which returns 444

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

FYI

=SUMPRODUCT((E7:G7>=D4)*(E7:G7<=E4)*(E8:G10))

I get it to return 1683 on the posted data using the above and 584 if "="
removed..

So just adjust ranges to suit e.g

=SUMPRODUCT((E7:R7>=D4)*(E7:R7<=E4)*(E8:R100))

HTH

 

[Peo Sjoblom]

I guess I must have copied data from the post incorrectly.

I copied this part

7/3 7/10 7/17
100 150 200
200 101 252
125 333 222

Total for each column is

425 444 674

gives a total of 1543 and 444 for the middle column

and the total of the values under the dates is 1543? You and the OP get
another 140 extra


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

Peo,
Middle column is 584. ... not sure how you got 444!

 

[Guest]

.... and why does SUMPRODUCT work in this situation when (in my understanding)
the array dimensions are not the same? We have same number of columns but
different number of rows.

I thought I understood SUMPRODUCT(!) ..but now ???

 

[Peo Sjoblom]

Doh! I don't know how I got that, I had 10 instead of 150 in the middle
column.

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Peo Sjoblom]

The result of the first 2 ranges is {1,1,1} and as long as the next array is
dividable by the first 2 it will work but =SUMPRODUCT({1,1,1}*(E8:F10))
will not and =SUMPRODUCT({1,1,1,1}*({100,150,200;200,101,252;125,333,222}))
will not work

That is the only time I multiply the ranges as opposed to using

--(range1=x),--(range2=y)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

Peo,
Another piece of the (enormous) Excel jigsaw fitted into place!
Thank you for getting back on this one - a useful one to remember.

P.S. Good to know even you experts have those "Doh" moments!

 

[Guest]

Thanks guys, worked like a charm!!!!!!

Doh! I don't know how I got that, I had 10 instead of 150 in the middle
column.

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey