In cells C2:C4 I have three start dates
In cells D2:D4 I have three end dates

In cells Q20:Q50 I have dates in random order
In cells R20:W50 I have numbers generated by other formula

In R51 I need to be able to Sum all the data in column R that is between the
start date in cell C2 and the end date in cell D2. I then would use the
same formula across row 51.
In R52 I need to be able to Sum all the data in column R that is between the
start date in cell C3 and the end date in cell D3. I then would use the
same formula across row 51
In R53 I need to be able to Sum all the data in column R that is between the
start date in cell C4 and the end date in cell D4. I then would use the
same formula across row 51

I suspect it may be maybe a sumproduct formula? I don't know.

For the first formula use.

=sum(R20:r50) - sumproduct((--(r20:r50<c2),--(r20:r50)) -
sumproduduct((--r20:r50d2),(r20:r50))

For the 2nd and 3rd formulas, substitute c3 and c4 for c2, and d3 and
d4 for d2.


--
rsenn
------------------------------------------------------------------------
rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050
View this thread: http://www.excelforum.com/showthread...hreadid=493231

Thank you for the formula. I guess I was on the right track as I thought it
was probably a sumproduct.

I will enter into the spreadsheet in the morning back at work.

In the formula I notice in several areas you have a -- is that correct?


"rsenn" wrote in
message ...

For the first formula use.

=sum(R20:r50) - sumproduct((--(r20:r50<c2),--(r20:r50)) -
sumproduduct((--r20:r50d2),(r20:r50))

For the 2nd and 3rd formulas, substitute c3 and c4 for c2, and d3 and
d4 for d2.


--
rsenn
------------------------------------------------------------------------
rsenn's Profile:
http://www.excelforum.com/member.php...o&userid=29050
View this thread: http://www.excelforum.com/showthread...hreadid=493231

Yes, the double minus signs tease the interim evaluated results in the
formula into a number.

I think I had a typo in the original formula, but you probably caught
it.

=sum(R20:r50) - sumproduct((--(r20:r50<c2),--(r20:r50)) -
sumproduduct(--(r20:r50d2),(r20:r50))


--
rsenn
------------------------------------------------------------------------
rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050
View this thread: http://www.excelforum.com/showthread...hreadid=493231

The formula unfortunetly did not accomplish what I needed. Perhaps I
did not explain it clearly, which I apologize for. I have attached a
excel sheet that I called Example.zip that hopefully addresses my two
problems that I have listed on the sheet. I hope someone can help me
resolve these issues.

Thanks again for all help provided.


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4123 |
+-------------------------------------------------------------------+

--
Frick
------------------------------------------------------------------------
Frick's Profile: http://www.excelforum.com/member.php...o&userid=29629
View this thread: http://www.excelforum.com/showthread...hreadid=493231

Frick,

I would break the problem down by using hidden cells to do some of the
calculations. For example to SUM for Semester#1 I would use:

=SUMPRODUCT(--(B13:B31=D7),--(B13:B31<=F7),C13:C31)

and to calculate either the lowest two from either Semester#1 or the Total
try simething like:

=IF(J7="S",SUM(SMALL(IF(--((B13:B31=D7)*--(B13:B31<=F7)*C13:C31)0,C13:C31),{1,2})),SUM(SMAL L(C13:C31,{1,2})))

which is an array formula committed by Ctrl + Shift + Enter

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Frick" wrote in message
...

The formula unfortunetly did not accomplish what I needed. Perhaps I
did not explain it clearly, which I apologize for. I have attached a
excel sheet that I called Example.zip that hopefully addresses my two
problems that I have listed on the sheet. I hope someone can help me
resolve these issues.

Thanks again for all help provided.


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4123 |
+-------------------------------------------------------------------+

--
Frick
------------------------------------------------------------------------
Frick's Profile:
http://www.excelforum.com/member.php...o&userid=29629
View this thread: http://www.excelforum.com/showthread...hreadid=493231

The first formula works great.

the second one is not returning the correct value. When J7=S I get
#Num and when J7=T I get a value of 6.12.

I entered the array correctly with CTRL-SHIFT-ENTER so that's not the
problem. I'm having a hard time understanding the formula as I have
not worked with array's before.

Can you look it over and see if I'm making some kind of error. I
susspect if we can get it too work for removing the lowest then iit
should not be difficult to remove the lowest two and lowest three.

Thanks again for your help.


--
Frick
------------------------------------------------------------------------
Frick's Profile: http://www.excelforum.com/member.php...o&userid=29629
View this thread: http://www.excelforum.com/showthread...hreadid=493231

Hi Frick,

I don't think that you are making any errors. If the SMALL function does
not find enough elements in the array it returns a #Num error which
overrides the other numbers being returned. That is why I suggested putting
it in a hidden row so that you can account for it being an error value when
you refer to it. Send me an e-mail by replacing the part after the @ as my
signature suggests and I will send you demonstration sheet that shows what I
intended.

I just noticed when writing the above that I had made mistakes in my
signature address which I have now corrected.
--
HTH

Sandy

with @tiscali.co.uk


"Frick" wrote in
message ...

The first formula works great.

the second one is not returning the correct value. When J7=S I get
#Num and when J7=T I get a value of 6.12.

I entered the array correctly with CTRL-SHIFT-ENTER so that's not the
problem. I'm having a hard time understanding the formula as I have
not worked with array's before.

Can you look it over and see if I'm making some kind of error. I
susspect if we can get it too work for removing the lowest then iit
should not be difficult to remove the lowest two and lowest three.

Thanks again for your help.


--
Frick
------------------------------------------------------------------------
Frick's Profile:
http://www.excelforum.com/member.php...o&userid=29629
View this thread: http://www.excelforum.com/showthread...hreadid=493231