Count unique values based on another column

[JGalper]

Hi everyone!

I have a Excel sheet with two (applicable) sheets:
The first sheet has a list of Job Codes in column A.

The second sheet has multiple columns, (starting with A): PO Number, PO Line, Item...then the last column is the Job Code Reference column.

The PO Number can repeat if there are multiple lines, and a Job Code can be referenced on more than one PO.

I am going to use another column on the first sheet for my formula which is where my question is:

I would like to use the Job Code value on sheet one to look up all instances of PO Numbers on the second sheet based on the Job Code Reference Column. If there is more than one referenced PO, return the string "Multiple", otherwise just return the one PO Number, even if there are multiple PO Lines.

How would I go about doing this?

Edit: Added example attachment

[enrand22]

mmmm you only need one formula (my excel is in spanish so you should review ortography before using it)

=if(countif(sheet2!A:A,a1)>1,"Multiple",sumif(sheet2!c:c,a1,sheet2!a:a))

[JGalper]

mmmm you only need one formula (my excel is in spanish so you should review ortography before using it)

=if(countif(sheet2!A:A,a1)>1,"Multiple",sumif(sheet2!c:c,a1,sheet2!a:a))

Take a look at the attached file. Maybe I am doing the formula wrong, but instead of saying multiple, it is adding up every single PO Number. Even for the PO it finds for row 4 on sheet 1 it is adding up the duplicate occurrences.

[enrand22]

that was my bad... but just change for this one

at first i was counting the column A:A instead of the column C:C

=if(countif(sheet2!c:c,a1)>1,"Multiple",sumif(sheet2!c:c,a1,sheet2!a:a)

here it is the attached correction.

[enrand22]

that was my bad... but just change for this one

at first i was counting the column A:A instead of the column C:C

=if(countif(sheet2!c:c,a1)>1,"Multiple",sumif(sheet2!c:c,a1,sheet2!a:a)

here it is the attached correction.