# Return "Invoiced Amt" when "PO Number" matches PO number in multiple columns

Options

Hi there,

Relatively new to SS here. I am trying to return the invoiced amount from sheet 1 when the PO number in sheet 1 matches the PO number in column 1, 2, and 3 in sheet 2.

I am currently using this formula =SUMIF({PO Number}, [PO Number 2024]@row, {Invoiced Amt}) but now the criterion needs to be from 3 columns, not just the one.

• ✭✭✭✭✭
Options

Hello @melaniem

Something like this perhaps?

=SUMIFS({Invoiced Amt}, {PO Number}, [PO Number 2024]@row, {PO Number 2}, [PO Number 2024]@row, {PO Number 3}, [PO Number 2024]@row)

• ✭✭✭✭✭✭
Options

Does the PO number need to match in columns 1, 2, AND 3 or would it be a match in 1, 2, OR 3? Is there a single PO column in the source sheet, or does that have multiple PO columns as well?

• edited 02/06/24
Options

@Paul Newcome the PO number in the source sheet is a single column, and that number needs to match a PO number in columns 1,2, or 3 to return a value.

• ✭✭✭✭✭✭
edited 02/07/24
Options

In that case, give this a try:

=SUMIF({PO Number}, OR(@cell = [PO Number 2023]@row, @cell = [PO Number 2024]@row, @cell = [PO Number Additional]@row), {Invoiced Amt})

I realized after posting that blanks may mess with the above. Below is a modification that should work better:

=SUMIF({PO Number}, OR(@cell = IF([PO Number 2023]@row <> "", [PO Number 2023]@row, "z"), @cell = IF([PO Number 2024]@row <> "", [PO Number 2024]@row, "z"), @cell = IF([PO Number Additional]@row <> "", [PO Number Additional]@row, "z")), {Invoiced Amt})

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!