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

melaniem

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.

Thanks in advance!

=Chris Palmer

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)

Paul Newcome

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?

melaniem

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

Paul Newcome

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