Incorrect data in Pivot table

Hello all!
I have a question. Due to my work I have to analyze quite big excel files, create pivot table and analyze them. Recently I encountered this feature:
Suppose I have this sample table:
No. Qty
0
1 10
1 10

If I create pivot table, it will show that Sum for "Qty" for No. 1 is 2.
If I remove the line with null Qty and refresh pivot table, it will again show that Sum for "Qty" for No. 1 is 2.
If I create new pivot table then it will show that Sum for "Qty" for No. 1 is 20 (the right one).

I attached sample Excel file with two kinds of pivot tables.

Is it a normal bevaviour of Excel or this is a bug? Does anyone encounter it before? Why there is no error messages or warning, I thought I could trust Excel pivot tables, now it seems that it can distort results without any warnings in some case.

Maybe somewhere is written that I cannot use pivot table for null data, if it is the case, where can I find it and all restrictions like that?

Thanks in advance!

Hi
in sheet 2 the field setting for the data field is set to "Count" instead of Sum. Just change this and it will be OK

BTW your sheet triggers a Security error relating to Data Connections. I didn't enable this for Security reasons

For safety, the attachment in post #1 that was generating security alerts was removed.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.