Weird Excel glitch?

[Tim4freedom]

I have a formula in a spreadsheet which starts with If(and(....,....) which references to date ranges to select cells, which then continues with sumif(...:...:...).
When I copy the formula into the cell above this, keeping all criteria exactly the same I get #VALUE! as the result. Even if I Cut, then Paste I get the same outcome, then even more strange, if I then Cut and Paste it back to the original cell below it now gives the correct answer.
The formula is obviously accurate as it gives the correct answer, until it is placed in the cell above.
Can anyone help with this strange situation?
I have manually typed in the same formula and it still gives the #VALUE! outcome!

 

[RoryA]

Glad to help.

 

[Tim4freedom]

Probably. At a guess (since I have no idea what's where in your sheet):
=SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),'Daily Job Tracker'!$E$6:$E$2000)/=SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),--('Daily Job Tracker'!$AM$6:$AM$2000>0))

Just one more question.

When I entered the formula when the sumproduct of AM6:Am2000 result = 0 then I get a #DIV/0 error. I always try to avoid these, so can you suggest a way I write a if/then formula at the start of this so that if the end part = 0 then it will just return a zero value instead of trying to divide by zero?

Thanks

 

[RoryA]

Either:
=IF(SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),--('Daily Job Tracker'!$AM$6:$AM$2000>0))=0,0,SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),'Daily Job Tracker'!$E$6:$E$2000)/SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),--('Daily Job Tracker'!$AM$6:$AM$2000>0)))

or:
=LOOKUP(9E305,choose({1,2},0,SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),'Daily Job Tracker'!$E$6:$E$2000)/SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),--('Daily Job Tracker'!$AM$6:$AM$2000>0))))

 

[Tim4freedom]

Either:
=IF(SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),--('Daily Job Tracker'!$AM$6:$AM$2000>0))=0,0,SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),'Daily Job Tracker'!$E$6:$E$2000)/SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),--('Daily Job Tracker'!$AM$6:$AM$2000>0)))

or:
=LOOKUP(9E305,choose({1,2},0,SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),'Daily Job Tracker'!$E$6:$E$2000)/SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),--('Daily Job Tracker'!$AM$6:$AM$2000>0))))

I used the first option as I am understanding it quite well but not the second one. It works!

Thanks for all your help Rory

 

[scottylad2]

or:
=LOOKUP(9E305,choose({1,2},0,SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),'Daily Job Tracker'!$E$6:$E$2000)/SUMPRODUCT(--('Daily Job Tracker'!$B$6:$B$2000>$A$8-1),--('Daily Job Tracker'!$B$6:$B$2000<$A$14),--('Daily Job Tracker'!$D$6:$D$2000=C5),--('Daily Job Tracker'!$AM$6:$AM$2000>0))))

Rory, I think I'm understanding the use of Choose here.....but can you enlighten? I've seen it used once or twice for this type, but not a 100% sure what it's doing. Can you explain please?

thanks

 

[RoryA]

Sure:
Using the {1,2} array as the first part of choose means it returns an array of two values (by choosing each of the two options in turn).
The first value is simply 0
The second is the result of the SUMPRODUCT formulas, which is either a number or #DIV/0

The lookup part then looks for a really high number in the returned array and returns the highest value - crucially, it ignores errors, so if the SUMPRODUCT returns #DIV/0, the LOOKUP ignores it and simply returns 0.

Make sense?

 

[scottylad2]

yes it does, thanks. Thats the other scenario i've seen it, I think it was Barry Houdini that used it instead of an error handler in a vlookup.

thanks for the explanation