Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula - help!
I'm using the following formula to sum entries with certain criteria. The
formula works fine up until the last bit, which is where I want to pick up all the entries that are greater than 10 days from the date I've specified. I'm wondering if I might need to use the last bit against each entry and then sum it from the =SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="3-AMBER"),--(ISNUMBER(MATCH($E$2:$E$191,{"C","D"},0))),--($I$2:$I$191<"ACTIVE"),--(25/10/2006-$F$2:$F$191{10})) Any advice gratefully appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula - help!
Kierano,
Try: --(DATEVALUE("25/10/2006")-$F$2:$F$19110) HTH, Bernie MS Excel MVP "Kierano" wrote in message ... I'm using the following formula to sum entries with certain criteria. The formula works fine up until the last bit, which is where I want to pick up all the entries that are greater than 10 days from the date I've specified. I'm wondering if I might need to use the last bit against each entry and then sum it from the =SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="3-AMBER"),--(ISNUMBER(MATCH($E$2:$E$191,{"C","D"},0))),--($I$2:$I$191<"ACTIVE"),--(25/10/2006-$F$2:$F$191{10})) Any advice gratefully appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula - help!
Thanks Bernie - sadly that didn't work, although if I reference only one cell
(in this case F2), a value of TRUE is returned. "Bernie Deitrick" wrote: Kierano, Try: --(DATEVALUE("25/10/2006")-$F$2:$F$19110) HTH, Bernie MS Excel MVP "Kierano" wrote in message ... I'm using the following formula to sum entries with certain criteria. The formula works fine up until the last bit, which is where I want to pick up all the entries that are greater than 10 days from the date I've specified. I'm wondering if I might need to use the last bit against each entry and then sum it from the =SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="3-AMBER"),--(ISNUMBER(MATCH($E$2:$E$191,{"C","D"},0))),--($I$2:$I$191<"ACTIVE"),--(25/10/2006-$F$2:$F$191{10})) Any advice gratefully appreciated! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula - help!
Try this:
--(DATEVALUE("25/10/2006")-SUM($F$2:$F$191)10) "Kierano" wrote: I'm using the following formula to sum entries with certain criteria. The formula works fine up until the last bit, which is where I want to pick up all the entries that are greater than 10 days from the date I've specified. I'm wondering if I might need to use the last bit against each entry and then sum it from the =SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="3-AMBER"),--(ISNUMBER(MATCH($E$2:$E$191,{"C","D"},0))),--($I$2:$I$191<"ACTIVE"),--(25/10/2006-$F$2:$F$191{10})) Any advice gratefully appreciated! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula - help!
Try forcing where the evaluation takes place:
--((DATEVALUE("25/10/2006")-$F$2:$F$191)10) HTH, Bernie MS Excel MVP "Kierano" wrote in message ... Thanks Bernie - sadly that didn't work, although if I reference only one cell (in this case F2), a value of TRUE is returned. "Bernie Deitrick" wrote: Kierano, Try: --(DATEVALUE("25/10/2006")-$F$2:$F$19110) HTH, Bernie MS Excel MVP "Kierano" wrote in message ... I'm using the following formula to sum entries with certain criteria. The formula works fine up until the last bit, which is where I want to pick up all the entries that are greater than 10 days from the date I've specified. I'm wondering if I might need to use the last bit against each entry and then sum it from the =SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="3-AMBER"),--(ISNUMBER(MATCH($E$2:$E$191,{"C","D"},0))),--($I$2:$I$191<"ACTIVE"),--(25/10/2006-$F$2:$F$191{10})) Any advice gratefully appreciated! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula - help!
Just ignore my previous post.
I took Bernie formula and test on my worksheet it works OK "Teethless mama" wrote: Try this: --(DATEVALUE("25/10/2006")-SUM($F$2:$F$191)10) "Kierano" wrote: I'm using the following formula to sum entries with certain criteria. The formula works fine up until the last bit, which is where I want to pick up all the entries that are greater than 10 days from the date I've specified. I'm wondering if I might need to use the last bit against each entry and then sum it from the =SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="3-AMBER"),--(ISNUMBER(MATCH($E$2:$E$191,{"C","D"},0))),--($I$2:$I$191<"ACTIVE"),--(25/10/2006-$F$2:$F$191{10})) Any advice gratefully appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Array formula and multiplying conditions | Excel Worksheet Functions | |||
SumProduct Formula Help | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |