SUMIFS AND CONTAINS

Jennifer Lindquist

I'm trying to sum the quantity column for all rows where the JO's match and the PN's match.

What am I doing wrong, it returns a 0? Here's the formula:

=SUMIFS(Quantity:Quantity, JO:JO, AND(CONTAINS(JO@row, @cell)), PN:PN, AND(CONTAINS(PN@row, @cell)))

Capture.JPG

Summer

@Jennifer Lindquist

Can you restate your ask? In your example there are no matches between to JO and PN columns.

Are you looking to SUM the Quantity for all instances where the PN Matches within it's own column and then the same for JO?

Jennifer Lindquist

This is what I need the Total Job Qty to look like.

I want to sum the quantity for the rows where the PN and JO match. Our operators weigh lots of skids in a job and I'd like to add them together. I'm ok if the Total Job Qty is repeated where there is a PN and JO match. I just need to add them up.

In this example, lines 1 and 4 PN and JO match, so it would add 300 and 300.

Lines 2 and 3 match, so it would add 250 and 250.

Jeff Reisman

@Jennifer Lindquist

I think you're over-engineering this. You don't need the AND or CONTAINS in there to just evaluate for a single text or numeric value in each range:

=SUMIFS(Quantity:Quantity, JO:JO, JO@row, PN:PN, PN@row)