SUMIF with additional parameter

JLen

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.

ShelbyWarren

@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 #.

ShelbyWarren

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

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

@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

@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

Glad it worked out 😀