SUMIF with additional parameter

Options

On one sheet I show PO#, Total amount USD, and the status of Approval. On a different sheet, I want to add up the "Total Amount USD" for each PO, but only if the approval column is showing "Approved."

I know the right SUMIF formula: =SUMIF({PO}, [PO Number]@row, {Total Amount USD}) but I don't know how to add in the parameter to only add if {{Approval}} = "Approved"


Thanks in advance.



Best Answer

  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    edited 04/24/23 Answer ✓
    Options

    @JLen Sorry, I misunderstood the solution you were creating, try this:

    =SUMIFS({Total Amount USD}, {Approval}, "Approved", {PO #}, PO)

    The SUMIFS formula allows you to add additional criteria, such as the "Approved" status and the PO #.

Answers

  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    edited 04/21/23
    Options

    Place this formula in your metric sheet and create the cross-sheet references:

    =INDEX(COLLECT({Total Amount USD}, {PO#}, PO#@row, {Approval}, "Approved"), 1)

  • JLen
    JLen ✭✭✭✭
    Options

    First, thank you, @ShelbyWarren - this works. However, when the {Approval} column is anything other "Approved" then I get an error: Invalid Value. There could be situations where 3 invoices for the same PO are approved but the fourth is not. How do adjust my formula to only count the invoice totals where the approval status is "approved" without getting an invalid value?

  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    edited 04/24/23 Answer ✓
    Options

    @JLen Sorry, I misunderstood the solution you were creating, try this:

    =SUMIFS({Total Amount USD}, {Approval}, "Approved", {PO #}, PO)

    The SUMIFS formula allows you to add additional criteria, such as the "Approved" status and the PO #.

  • JLen
    JLen ✭✭✭✭
    Options

    @ShelbyWarren - thank you so much. This works! I had a SUMIFs but I had the syntax wrong. Such a silly mistake - thank you for steering me in the right direction!

  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    Options

    Glad it worked out 😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!