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]

What is the formula?

 

[Ruddles]

And where is it?

 

[Tim4freedom]

Re: Weird Excel glitch? - the Formula

I am very new to forums, so sorry for the lack of information

Here is the specific formula I have used in cell C6 of a worksheet and if entered into C5 of the same worksheet it returns #VALUE!...

=IF(AND('Daily Job Tracker'!B6:'Daily Job Tracker'!B2000>A8-1,'Daily Job Tracker'!B6:'Daily Job Tracker'!B2000<A14),SUMIF('Daily Job Tracker'!D6:'Daily Job Tracker'!D2000,C4,'Daily Job Tracker'!E6:'Daily Job Tracker'!E2000),0)

Thanks

 

[RoryA]

Re: Weird Excel glitch? - the Formula

You appear to be missing parts of the post - are you array entering the formula using Ctrl+Shift+Enter?

 

[shg]

That formula doesn't make sense to me unless there's a lot of it missing. If there's a < symbol, bracket it with spaces.

 

[Tim4freedom]

That formula doesn't make sense to me unless there's a lot of it missing. If there's a < symbol, bracket it with spaces.

Does this help? Sorry if I appear ignorant as I have not posted messages on forums prior to this post.

=IF(AND('Daily Job Tracker'!$B$6:'Daily Job Tracker'!$B$2000 > $A$8-1,'Daily Job Tracker'!$B$6:'Daily Job Tracker'!$B$2000 < $A$14),SUMIF('Daily Job Tracker'!$D$6:'Daily Job Tracker'!$D$2000,C4,'Daily Job Tracker'!$E$6:'Daily Job Tracker'!$E$2000),"No")

In this formula, C4 refers to text, which is "Land-scaping". Daily Job Tracker is another worksheet.

$A$8 and $A$14 are references to dates. Column B in the 'Daily Job Tracker' worksheet contain Dates in the format of 14/02/2011 etc.
Column D in 'Daily Job Tracker' contains text, such as Land-scaping and other text.

With this formula I am checking in 2 columns of Daily Job Tracker that the date is between a range A8-1 and A14 and also "Land-scaping", if both criteria are fulfilled then add up what is in Daily Job Tracker column D.

Hope this clarifies the question.

 

[RoryA]

You would need to array enter that for it to work, but it's not doing what you think (assuming I understood your requirement). I believe you want:
=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)

 

[Tim4freedom]

Hi Rory, thanks for the suggestion.
When I enter it I get a zero , but the answer is not zero.
Is this formula when you want to multiply columns by columns as I don't want to do that.

What I want to do is make sure 2 lots of criteria are satisfied in rows 6:2000, i.e. Date (Daily Job Tracker column B) and Job Type (column D) and when both criteria are satisfied in a row then add up the figure in column E6:E2000.

I may not be explaining myself well, but hopefully this helps. I am taking out the "--" when entering your suggested formula.

 

[RoryA]

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