# Weird Excel glitch?

#### Tim4freedom

##### New Member
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!

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### RoryA

##### MrExcel MVP, Moderator
What is the formula?

And where is it?

#### Tim4freedom

##### New Member
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

##### MrExcel MVP, Moderator
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP, Moderator
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

##### New Member
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

##### MrExcel MVP, Moderator
No, you have to leave the -- in the formula for it to work.

Replies
1
Views
56
Replies
0
Views
124
Replies
12
Views
340
Replies
3
Views
159
Replies
7
Views
97

1,191,374
Messages
5,986,274
Members
440,016
Latest member
vasanrajeswaran

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back