Array Formula or Pivot Table

I am using Excel 2003. I have a list of data that is retrieved from an external data source. From this data I want to show on a separate worksheet ONLY the Cons Unit/Partner Unit combination where the total of the combination is less than zero. I am able to do this with multiple pivot tables and advanced filters, but I am thinking that this could be done in fewer steps using array formulas. I am not very familiar with array formulas, but from what I have gleaned from this forum, I think it should work if I use the right combinations of formulas. I am just at a lost which ones I should use. The attached file includes a sample of the raw data from the external data source and the result I am trying to achieve in as few steps as possible.

Any help is greatly appreciated and I apologize for my inexperience. I am sure there is probably an easy solution to this.

Find the attached as a possible solution.

There is a problem with your data. When you import it you need to make the text imports numbers or values. If they come in as numeric then pivot tables will work. If some are text and some values then the problem gets harder.

Thank you for your response, Marvin P.

The Cons Unit, Partner Unit, and Item fields are identifiers, not numbers or values. They are imported with the "General" format. The results should display a total for each Cons Unit/Partner Unit combination, i.e. 200/25, 200/30, but ONLY if the net total is a negative number. Your solution is displaying all of the totals for the Cons Unit, regardless of the Partner Unit or that the net Partner Unit total is negative. Any other suggestions?

I do appreciate the response, however!

Hi numberchomper,

Find the attached that now does what I think you want. The first problem was that some of your Cons data was numeric and some was text. You need to import the Cons data as all Text first.

Then I struggled with only negative amounts but it works with a Amt filter needing a "(" deep in the pivot table tree. I also had to "drag sort" your Cons in the Pivot Table as it is now Text.