Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif plus (items/#days til date)
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)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif plus (items/#days til date)
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$664000date(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$64000date(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 "JL InRe" wrote: 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)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif plus (items/#days til date)
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(D1date1),(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! "JL InRe" wrote: 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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
report date - date rec.ved=days late, but how to rid dates complet | Excel Worksheet Functions | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Sumif only unique items | Excel Discussion (Misc queries) | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions |