formula results are numbers but mostly do not act as numbers

Hello!

I need to calculate net cost and I have always used formula like that =AH3*(1-VLOOKUP(A3;Abi!$A$1:$B$10;2;FALSE)) Vlookup part will give me right discounts. Anyways this formula has always worked and now it does not. Problem occurs when I am using Pivot Table and net cost is only count, cannot sum. But in data file I can add different net costs together and it sums them. Though when this column is activated only count is shown ( not average and other). It seems like that formula results are numbers but at the same time are not. Has anybody run into this kind of problem?

Thank you for your time

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

That's better, thanks.

The most likely issue is text that looks like a number.

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Also, even if the pivot table defaults to count (which could be for many reasons such as including blank rows in the data source range), you should be able to change it to use Sum. If you do that, does it return the correct values?

You have been asked to attach the workbook here. Please do so.

That just means that at least one of your formulas is returning a #N/A error due to the VLOOKUP failing. If that is not expected, you need to figure out why; if it's a possibility, you need to add an error handler to your formula to return a default value like 0 or "" in such cases.