SUMPRODUCT formula - help!

[Guest]

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 there:

=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!

 

[Bernie Deitrick]

Kierano,

Try:

--(DATEVALUE("25/10/2006")-$F$2:$F$191>10)

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Bernie - sadly that didn't work, although if I reference only one cell
(in this case F2), a value of TRUE is returned.

 

[Guest]

Try this:

--(DATEVALUE("25/10/2006")-SUM($F$2:$F$191)>10)

 

[Bernie Deitrick]

Try forcing where the evaluation takes place:

--((DATEVALUE("25/10/2006")-$F$2:$F$191)>10)

HTH,
Bernie
MS Excel MVP

 

[Guest]

Just ignore my previous post.

I took Bernie formula and test on my worksheet it works OK