Sumif concatenated criteria does not work in one tab, but works in all others

[Rajwah]

I have encountered a problem with a sumif formula that I have never seen before. I created the formula on Tab A, using data on tab A, and
I get 0 as a result. However, when I recreate the formula on Tab B (referencing the data on Tab A), it works. What is really strange is
that if I take the formula from Tab B and cut and paste it into Tab A, it no longer works, but I can cut and paste the formula from Tab A
to any other tab and it works. The problem is with the criteria. The criteria I want to use is
"<" & A2
If I change it to "<10" it works. The value in A2 is 10.

I have been using spreadsheets for over 30 years, and this is the strangest problem I have ever seen, and the first time I have ever gone to a
forum for help. Any ideas would be greatly appreciated.

[bebo021999]

Is A2 value? try to test it with: =isnumber(A2)
If it is text try "<"&A2+0
or "<"&VALUE(A2)

[Rajwah]

It is definitely a value. That was the first thing I checked.

It is so odd that the formula (using the same range, criteria, and sum range) works in all tabs but one.

Also, last night I did a test by copying the entire Tab A to a new Tab C, and the formula works in Tab C. Very strange. Unfortunately,
it would be a major effort to transition all the links into Tab A over to Tab C, so I'm still trying to fix the problem. Also, I would
just like to know what the problem is.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[Fluff13]

Sounds like you might have a circular reference.

[Rajwah]

I have attempted to attach a file which shows the problem. Note that the sumif formula on Tab A (cell A1) is exactly the same formula on
Tab B, but B gives the correct result and A gives 0. If you open the Formula Builder it is showing #VALUE! for criteria.

[Rajwah]

Definitely not a circular reference.

[Fluff13]

Definitely something odd going on. I can only think that the sheet has been corrupted in some way.

[AliGW]

Very odd!

It works without the "<"& ...

[Pepe Le Mokko]

I opened the document with LibreOffice, results were OK. (posted sheets is xls)
Then saved as an xlsx file and it works. Am I missing something ?

[bebo021999]

Not sure what Lotus is, but uncheck this box then it works.

(P/S: It is from huuthang_bd in our local Excel help forum. Tks)

[AliGW]

Lotus is (was) an office application that included a spreadsheet app. I had Lotus Works (I think) on my very first PC, and it worked in DOS, not Windows. Good spot!

[Rajwah]

OK, I'm impressed. I had never seen "Transition formula evaluation", but unchecking the box fixed the problem. I never would have
found that myself. Kudos to bebo021999.

Based on this solution I did some research, and the problem seems due to the original version of this spreadsheet. The original version
goes all the back to about 1990, and was created in Lotus 1-2-3. It is a budget file that I have updated every year since then.
Some time in the late 1990s I switched from Lotus to Excel by just opening the Lotus file in Excel and saving it as a .xls file.
I've never had a conversion problem with it until now. I still don't know exactly why the formula doesn't work with the box checked,
but when users have a problem with a formula working in one tab but not another, when the formula is exactly the same, it is certainly
worth asking if the original version of the file was created in Lotus.

As an aside, I had to smile at you youngsters who don't know what Lotus was. Lotus 1-2-3 was the de facto standard for spreadsheets until
Excel supplanted it in the mid 90s. I first saw it in 1984 and it changed my life.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Jhowe24]

Hi All - I know this has already been solved, but I just want to say that the same issue has been plaguing me for days and this solution fixed it. Thank you!