Sum multiple lines based on multiple criteria

[JimS]

I need 2 different formulas that can look at multiple rows and do a sum based on if a condition exists (or does not exist) at the individual order level.

The rows are orders and each order can have multiple rows.

The attachment explains what I’m trying to do.

Thanks for any help.

JimS

[Bob Phillips]

The first would be

=SUMIF(Data!B:B,"P1",Data!D:D)

but I don't see a Qual of Blue.

[JimS]

Wouldn't this just sum the rows with P1?

=SUMIF(Data!B:B,"P1",Data!D:D)

I need all the rows that make up an order sumed if any of the rows of an order has a P1.

As far as the Blue they're in there at cells:
C135, C147, C196, C388, C684 and C872

This second formula would also need to look at all the rows of each order to determine if there is a Blue.

I'm thinking a Frequency & Match formula but cannot figure it out.

Thanks...

[Bob Phillips]

I would just pivot the data

[JimS]

I need a formula - this is just half of the equation that I'm building.

[JimS]

Tried building a pivot but cannot get it configured in a way that I can get the totals I need on a consistent basis.

Any other ideas?

JimS

[Bob Phillips]

Here's an example that pivots the first requirement http://cjoint.com/?0mowSyQZS6o

[JimS]

Thanks XLD.

I'm just not going to be able to use a Pivot Table for this.