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!

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!

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!

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!

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!

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!