MrExcel Publishing
Your One Stop for Excel Tips & Solutions

suming only negative numbers in a pivot table


Posted by Bruce Campbell on February 07, 2002 12:59 PM

Background: I have a spread sheet where all customer orders by part number are sorted by the due dates of the orders. I've calculated a running balance of items on hand if all prior orders have been filled with a sumif statement.

To simplify the view of the data. I've created a pivot table. It has part number as a row heading and due date as the column heading. In the data section it totals orders due. It also lists the minimum quantity for the running balance. Thus giving a projected balance on hand for each date.

Problem: The total line for the pivot table totals orders (this is good) and give the minimum of the running balances for all part numbers. I would like to change this to total up the negative values from the running inventory balances. I haven't figured out how to do that.

Can anyone help?


Posted by Mark W. on February 07, 2002 1:37 PM

Try inserting...

...a Calculated Field using something like...

=IF('Running Balance'<0,'Running Balance',0)