Formula too long gives me error

[Matthewious2016]

My formula is too long to calculate. Please help

Please Login or Register  to view this content.

Sometimes the result will appear but sometimes it wont. When I relocated this file, the first time opening when relocating the file the result will appear but subquently it wont appear again.

[Richard Buttrey]

I doubt it's too long, particularly since it sometimes works and sometimes doesn't.

We can't be expected to work out what's wrong unless we can see your problem in the context of the workbook.

So please upload the workbook and tell us on what occasions it doesn't produce a result. I'm guessing the file contains macros given your comment about relocating the file but that is just a guess.

[Matthewious2016]

I have attached the file.

[Richard Buttrey]

Hi,

Please explain what you are trying to do and what result you expect for the G13 cell. Please give some more examples that cover all permutations. Two rows only is usually not sufficient.

I feel certain that however you are trying to analyse the Order intake there is a simpler way. For instance have you considered using a helper column in the order intake sheet and creating the text string that you want to sum (or count).

[Matthewious2016]

So basically this formula must sum up when the product name is AS90 and AS91, and the brand must be SUN, and when is week 35. That is the formula trying to do

[Richard Buttrey]

Hi,

I can see a reference to SUN in the data in column B and presumably the 35 is the first two characters in column L, but where is the product name? I can see no reference to that text in the Order intake nor even the values AS90 or AS91 - where are they.

You are not making this very easy for us. I asked for some more examples, i.e. a longer set of data with different values which you have not given so pleased add those and then tell us what values you expect for different inputs.

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

[Matthewious2016]

Techonology is the product name, which is on the order intake Coloumn P. And for "AS90/AS91" which combining those two together according to the order type on the mainpage. Hopefully I had explained clearly to you.

The outcome will be adding these two AS90 and AS91 technologies together from the order intake sheet

[Richard Buttrey]

You really aren't making this very easy. We don't do this for a living so you need to help us to help you.

Your latest workbook contains just the MainPage sheet. The Order Intake is missing. Are you expecting me to combine the two? Furthermore you are now including AS90 & AS91 on the same row in C7 - that just complicates matters and is different to your original. You should put these on separate rows and then add them. But it's still not clear where AS90 & AS91 exist on the Order Intake sheet.

Would you do as I asked please.
Upload your actual workbook, the one with your real data in the real layout and make sure it contains at least a representative subset of your actual production data rows.

Then assuming the Main Page is the sheet on which you want to obtain the results, manually add all the results you expect to see based on the Order Intake sheet

[Matthewious2016]

I already attached the latest workbook on the last post. Here i will attached again.

[Richard Buttrey]

That's better. See attached

Personally I'd use a Pivot Table and if P090 & P091 are always together then use the P090/P091 value in the technology column of the Order Intake.

[Matthewious2016]

Is this the best way? What if i did not add pivot tables and just using only one formula?