Excel 2007 : Return a value if two columns have the same text

I have a large worksheet with 4 columns, like this:

john cups blue 2
john cups red 1
john cups all 3
john bowls green 4
john bowls yellow 1
john bowls all 5
john all all 8


This report gives me customer name, the category of product, the unique product within the category, the quantity of each unique product sold and the total for all products sold to each customer, first by product category, then by all products together.

The format continues for each successive customer.

The entries in the category and product columns are pre-set, and the only thing that defines the total row I want is the fact that the word "all" appears twice in that row (all categories and all products)

Is there a way to pull out just the total lines (all and all)? I tried using pivot tables but since there is so much repetition in the worksheet, it's not sortable. Is there some sort of "if all appears twice, then tell me the number in the next column" method to solve this.

Sorry if I've explained this poorly...thanks.

Not sure how you're using this but something like:

=SUMIFS(D:D,A:A,"John",B:B,"all",C:C,"all")

would give you a result of 8 if in column D there was an 8, col A contained "John", and both B and C contained "all"

and it would add to the 8 if the same conditions were met elsewhere in the table

so to get "John Bought 8 things total" place John in a cell (say G1)

in another cell place this:

=G1&" bought "&SUMIFS(D:D,A:A,G1,B:B,"all",C:C,"all")&" things total"