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!

 

[Tim4freedom]

No, you have to leave the -- in the formula for it to work.

Rory, it works!
Now can you explain why it does as I don't understand the formula? I don't know much about Sumproduct, and can't understand the "--" you wanted in it.

Thanks
Tim

 

[Domski]

Have a read of this:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Dom

 

[Tim4freedom]

Rory, it works!
Now can you explain why it does as I don't understand the formula? I don't know much about Sumproduct, and can't understand the "--" you wanted in it.

Thanks
Tim

I have one more challenge...
In another row, I need to calculate the value as an average.

Where the formula you suggested ends with 'Daily Job Tracker'!$E$6:$E$2000 I need it to end with 'Daily Job Tracker'!$E$6:$E$2000/(COUNTIF('Job Tracker'!$AM$6:$AM$2000,">0")

Can you suggest how I can use this style of formula or how to average column AM6:AM2000 ?

Thanks

 

[Tim4freedom]

Have a read of this:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Dom

Thanks Dom that's a great information page

 

[RoryA]

What are you trying to average specifically?

 

[Tim4freedom]

What are you trying to average specifically?

In the Daily Job Tracker worksheet it has a column of hrly rates of employees. These vary from job to job because different employees have different hourly rates on different jobs. So for Land-scaping there might be employees from $20/hr to $28/hr. The previous formula would add up all hourly rates in the column. I have the averages calculated across the row already, so adding them down the column isnt what I want, but the average hourly rate for all Land-scaping jobs.

Hope that makes sense.

 

[RoryA]

So where does column AM on 'Job Tracker' sheet come into it?

 

[Tim4freedom]

So where does column AM on 'Job Tracker' sheet come into it?

Sorry my mistake. I meant it to read 'Daily Job Tracker', as previously mentioned.
Looking at the problem I am imagining it needs to divide the first formula by a similar SUMPRODUCT one??

 

[RoryA]

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))

 

[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))

That's it! Brilliant You saved me hours of research.

Thanks Rory