sumif plus (items/#days til date)

[Guest]

Given this table
A B C D
1 1717 1697 20 3/15/07
2 934 839 95 3/30/07
3 853 594 259 2/23/07

I need the sum of B and C (adding C only if D is between 2 dates) plus for
dates in D > a certain date (2/26/07) take C divided by # of days between
today and that date in D. I need 1 lump sum. I have a formula that works,
but I do not want to enter C/#days for each row as the real sheet is roughly
28 rows and growing. Is there a simpler way? For your knowledge the formula
that partially works is: =IF('2-19-07'!$Y$3>'Overall
Completion'!$A$6,('2-19-07'!$U$3/DAYS360(TODAY(),'2-19-07'!$Y$3))+SUM('2-19-07'!$T$3:$T$28)+SUMIF('2-19-07'!$Y$3:$Y$28,"<"&$A$7,'2-19-07'!$U$3:$U$28),SUM('2-19-07'!$T$3:$T$28)+SUMIF('2-19-07'!$Y$3:$Y$28,"<"&$A$7,'2-19-07'!$U$3:$U$28))

 

[Guest]

assuming in your example and formula column T is B Column U is B and Column
yY is d
try
=SUM( ('2-19-07'!$T$3:$Y$64000
)+sumproduct(--(('2-19-07'!$Y$3:$Y$664000>date(date1),--('2-19-07'!$Y$3:$Y$64000<date(date
2),'2-19-07'!$U$3:$U$64000)+sumproduct(--(('2-19-07'!$Y$3:$Y$64000>date(date3),'2-19-07'!$U$3:$U$64000/(days360(TODAY(),'2-19-07'!$Y$3:y64000))

I think Date3 is 'Overall Completion'!$A$6
Date1 is $A$7
I can't tell what date 2 from your equation
Plus in your equation you are double summing the column C for the dates
grater than Date 3. If you want to do this, the above equation needs to be
changed

 

[Guest]

Can you use a brute-force method? Just split the operations up into smaller
chunks. In Column E put in a formula like:
IF(OR(D1>date1),(D1<date2)),SUM(B1,C1),0). In column F put in something
like: IF(D1=date3,C1/(TODAY()-date3),0). Then copy alongside all of the rows
with data and sum columns E and F. Somewhere off to the side define three
cells as date1, date2, and date3 and put in whatever dates meet your
criteria. It's ugly, but hey, if it gets the right answer...

Good luck!