The value of the pivot table sum is not working.
La
-
Arthur-92 -
Arthur-92 -
Hello,
The sum value of my Pivot Table is not working: I have incorrect sums.
My table consists of the following:
- 1 tab "Pivot Table" with 2 columns: 1 column "WORK CODE" and 1 column "QUANTITIES".
- 1 tab "raw data" (this is the table I want to use in the Pivot Table to classify the data).
For example, for the code TECME007, it shows 0 quantities in my Pivot Table while if we refer to the "raw data" tab of my Excel table, summing TECME007 gives 2,330.
Do you know where my error is? I have tried in vain but cannot find it.
Link to access my table: https://www.cjoint.com/c/LFEojw1zYQc
Thank you in advance
Best regards
The sum value of my Pivot Table is not working: I have incorrect sums.
My table consists of the following:
- 1 tab "Pivot Table" with 2 columns: 1 column "WORK CODE" and 1 column "QUANTITIES".
- 1 tab "raw data" (this is the table I want to use in the Pivot Table to classify the data).
For example, for the code TECME007, it shows 0 quantities in my Pivot Table while if we refer to the "raw data" tab of my Excel table, summing TECME007 gives 2,330.
Do you know where my error is? I have tried in vain but cannot find it.
Link to access my table: https://www.cjoint.com/c/LFEojw1zYQc
Thank you in advance
Best regards
10 réponses
Where do the numbers you entered come from?
First of all, the standard is to right-align numbers. If these are uploaded numbers, make sure they are in number format and not text format, and possibly use the cnum() function to get them in the correct format.
As for the second question, I don’t see it. Double-check your formulas...
First of all, the standard is to right-align numbers. If these are uploaded numbers, make sure they are in number format and not text format, and possibly use the cnum() function to get them in the correct format.
As for the second question, I don’t see it. Double-check your formulas...
Good evening Lola, and welcome among the new members.
yclick provided you with the solution: "try replacing . with ,"
Arthur gave you the answer: "check that they are in number format and not in text format and possibly use the cnum() function to get numbers in the correct format."
Indeed, even when replacing
Now you only have numeric data, and not a mix of numbers and texts ...
--
It’s great, retirement! Especially in the West Indies...
Raymond (INSA, AFPA)
Do you know where my mistake is? I have tried in vain, I can't find it..
yclick provided you with the solution: "try replacing . with ,"
Can you tell me what was wrong with my table??
Arthur gave you the answer: "check that they are in number format and not in text format and possibly use the cnum() function to get numbers in the correct format."
Indeed, even when replacing
.with
,, in a Text data, it remains in Text format. You must then enter the number 1 in an available cell; you do Copy; select columns B, C, and D; you do Paste Special Multiply.
Now you only have numeric data, and not a mix of numbers and texts ...
--
It’s great, retirement! Especially in the West Indies...
Raymond (INSA, AFPA)
Hello Raymond,
- I replaced the periods with commas (Search: . and replace with ,)
- For the second point, I don't know how to use the cnum() function, I would like it if someone could explain how this function works concretely (I looked it up online, but I didn't quite understand).
I understand why I need to multiply by 1 (that way I keep the values in the cells), however, I can't find the special paste for multiplication (attached is a screenshot). When I do a special paste, it suggests converting it to text (unicode text or text), is it because my data is already numeric?
- I replaced the periods with commas (Search: . and replace with ,)
- For the second point, I don't know how to use the cnum() function, I would like it if someone could explain how this function works concretely (I looked it up online, but I didn't quite understand).
I understand why I need to multiply by 1 (that way I keep the values in the cells), however, I can't find the special paste for multiplication (attached is a screenshot). When I do a special paste, it suggests converting it to text (unicode text or text), is it because my data is already numeric?
(... continuation)
That's exactly it: Just because you've hidden some rows with your filter doesn't mean that the values they contain escape the operation!
If you want to sum only the displayed rows (after filtering), you need to use the formula
=SUBTOTAL(9;B2:B400)
which results in 270.70 (and certainly not 2,330, which comes from who knows where!) ...
--
Retirement is great! Especially in the West Indies...
Raymond (INSA, AFPA)
I filtered my data, I believe this sum function takes into account the data that is adjacent...
That's exactly it: Just because you've hidden some rows with your filter doesn't mean that the values they contain escape the operation!
If you want to sum only the displayed rows (after filtering), you need to use the formula
=SUBTOTAL(9;B2:B400)
which results in 270.70 (and certainly not 2,330, which comes from who knows where!) ...
--
Retirement is great! Especially in the West Indies...
Raymond (INSA, AFPA)
Hello
It seems that not all decimal separators are the same
there are . and ,
see decimal separator settings in Excel
test by replacing . with ,
It seems that not all decimal separators are the same
there are . and ,
see decimal separator settings in Excel
test by replacing . with ,
Hello,
Please check if this table, with correctly entered data, produces the expected results:
https://cjoint.com/c/LGbj6xUXKOP
Best regards.
Please check if this table, with correctly entered data, produces the expected results:
https://cjoint.com/c/LGbj6xUXKOP
Best regards.
Hello,
It works, thank you so much! Can you tell me what was wrong with my table (so that I don't make the same mistake next time)?
I also have a small problem with the function =SUM:
When I use the function =SUM(.....:.....), I get an incorrect result, whereas when I do it "manually" by adding the cells one by one =B1+B2+etc. I get the correct result. I have filtered my data, and I believe this sum function takes into account the data that is adjacent to the cells I am interested in (I'm not sure if I'm clear, but below is a screenshot).
So, do you know how I could use the =SUM function without having this issue?
Thank you in advance,
Best regards
It works, thank you so much! Can you tell me what was wrong with my table (so that I don't make the same mistake next time)?
I also have a small problem with the function =SUM:
When I use the function =SUM(.....:.....), I get an incorrect result, whereas when I do it "manually" by adding the cells one by one =B1+B2+etc. I get the correct result. I have filtered my data, and I believe this sum function takes into account the data that is adjacent to the cells I am interested in (I'm not sure if I'm clear, but below is a screenshot).
So, do you know how I could use the =SUM function without having this issue?
Thank you in advance,
Best regards
It's all good, I found out what was wrong (I had entered the number 1 in a single empty cell while my table contains + 1 cells, so I needed to enter the number 1 in each empty cell so that the number of empty cells would be equivalent to the number of "filled" cells making up my table...). The special paste multiplication works!
Thank you so much for your time and your explanations!
Thank you so much for your time and your explanations!
check if there is an apostrophe in front of the numbers after data import
Select a cell containing a number and see in the formula bar if there's an apostrophe in front