Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct between two dates
I have a Date column, a percentage column, and a balance column. I want to
add all balances between a specific date to 30 days after that date and all balances between those dates above a certain percentage. Date Pctg. Balance 7/15/05 50% 10000 7/29/05 75% 15000 7/31/05 75% 15544 8/08/05 50% 12344 I want the formula to review from 7/15/05 to 8/15/05 and everything above 50% and add the balances that qualify. |
#2
|
|||
|
|||
Hi
Try something like: =SUMPRODUCT(--(A2:A50DATE(07,15,2005))*(A2:A50DATE(07,15,2005) )*(B2:B500.5),(C2:C50)) Andy. "chased" wrote in message ... I have a Date column, a percentage column, and a balance column. I want to add all balances between a specific date to 30 days after that date and all balances between those dates above a certain percentage. Date Pctg. Balance 7/15/05 50% 10000 7/29/05 75% 15000 7/31/05 75% 15544 8/08/05 50% 12344 I want the formula to review from 7/15/05 to 8/15/05 and everything above 50% and add the balances that qualify. |
#3
|
|||
|
|||
Sorry, got it wrong! Try this:
=SUMPRODUCT(--(A2:A50=DATE(07,15,2005))*(A2:A50<=DATE(08,15,200 5))*(B2:B500.5),(C2:C50)) Andy. <Andy wrote in message ... Hi Try something like: =SUMPRODUCT(--(A2:A50DATE(07,15,2005))*(A2:A50DATE(07,15,2005) )*(B2:B500.5),(C2:C50)) Andy. "chased" wrote in message ... I have a Date column, a percentage column, and a balance column. I want to add all balances between a specific date to 30 days after that date and all balances between those dates above a certain percentage. Date Pctg. Balance 7/15/05 50% 10000 7/29/05 75% 15000 7/31/05 75% 15544 8/08/05 50% 12344 I want the formula to review from 7/15/05 to 8/15/05 and everything above 50% and add the balances that qualify. |
#4
|
|||
|
|||
=SUMPRODUCT(--(A2:A20=--"2005-07-20"),--(A2:A20<=--"2005-7-20"+30),--(B2:B2
050%),C2:C20) -- HTH Bob Phillips "chased" wrote in message ... I have a Date column, a percentage column, and a balance column. I want to add all balances between a specific date to 30 days after that date and all balances between those dates above a certain percentage. Date Pctg. Balance 7/15/05 50% 10000 7/29/05 75% 15000 7/31/05 75% 15544 8/08/05 50% 12344 I want the formula to review from 7/15/05 to 8/15/05 and everything above 50% and add the balances that qualify. |
#5
|
|||
|
|||
Thank you very much!! Both formulas worked.
"Bob Phillips" wrote: =SUMPRODUCT(--(A2:A20=--"2005-07-20"),--(A2:A20<=--"2005-7-20"+30),--(B2:B2 050%),C2:C20) -- HTH Bob Phillips "chased" wrote in message ... I have a Date column, a percentage column, and a balance column. I want to add all balances between a specific date to 30 days after that date and all balances between those dates above a certain percentage. Date Pctg. Balance 7/15/05 50% 10000 7/29/05 75% 15000 7/31/05 75% 15544 8/08/05 50% 12344 I want the formula to review from 7/15/05 to 8/15/05 and everything above 50% and add the balances that qualify. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct and list of dates | Excel Discussion (Misc queries) | |||
SUMPRODUCT ON DATES | Excel Worksheet Functions | |||
sumproduct between dates | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |