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)))


  • Summer
    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?

  • 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
    Jeff Reisman ✭✭✭✭✭✭
    edited 04/27/22

    @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)


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!