Can you help with excel forumlas not executing properly?

Hey - yes it is set to automatic.

And more strangely I have 5 tabs which are duplicates (apart from data) and it only happens on some of them, some of the time.

Thank you Paul. I will try SUMPRODUCT and see if this reoccurs.

I'm confident there is no macros. But what steps could I take to assure this?

See attached image

Attachment 645509

Hello - please see attached image. I have clicked into each object and they're all without any code.

Attachment 645510

Sorry! I didn't realise you couldn't see the image. I thought you meant invalid as in not want you needed to see.


4.PNG

5.PNG

Originally Posted by mcuzz

=SUMIFS($D$3:$D$1000,$I$3:$I$1000,"Complete",$L$3:$L$1000, ">=01/06/2019",$L$3:$L$1000, "<01/07/2019")

First, it would be "better" (more flexible) if you wrote the formula as follows:

=SUMIFS($D$3:$D$1000,$I$3:$I$1000,"Complete",$L$3:$L$1000, ">="&DATE(2019,6,1),$L$3:$L$1000, "<"&DATE(2019,7,1))

As you wrote it, your formulas would sum "Complete" entries only on Jan 6 (i.e. between midnight Jan 6 and midnight Jan 7) on my computer, because 1/6/2019 would be interpreted in the form MDY instead of DMY.

Second, however, I do not see how your formula would work when copied down TCV or SOV columns, since the dates are hardcoded.

If the month names Apr, May etc are entered into column A, I suggest that you enter dates in the form (DMY) 1/4/2018, 1/5/2018 etc, formatted as Custom mmm to display only the month name (abbreviated).

Then your formula can be:

=SUMIFS($D$3:$D$1000,$I$3:$I$1000,"Complete",$L$3:$L$1000, ">="&$A3,$L$3:$L$1000, "<="&EOMONTH($A3,0))

That assumes that you have only dates, not times of day, in column L.

I cannot say if that would correct your problem. Probably not. But it certainly simplifies formulas and makes them less error-prone.

Off-hand, I see no reason why your formula would behave as you describe, except: (a) the cell format was Text originally, in which case you would see the actual formula, not merely a (old?) sum for Dec only; or (b) the calculation mode is Manual, or (very unlikely) the worksheet calculation is disabled (in VBA).

So, unless and until you can attach an Excel file that demonstrates the problem, I cannot help you.

PS.... I cannot see any good reason to change to a SUMPRODUCT formula. And SUMIFS is much more efficient.

Hello everyone.

Thank you for your support so far. I will make the suggested changes to my dates. As this problem only seems to occur infrequently - next time it does I will save a copy and attach it to this thread.

Just for clarity each month the formula is modified accordingly. So in jan row it is >=01/01/2019 and <01/02/2019, and feb >=01/02/2019 and <01/03/2019 etc

Hi everyone.

Wanted to let you know the bug seems to have been solved by changing the date from a string to a date object

01/03/2019 is now DATE(2019/03/01)


Thanks!