Calculate Cells based on information is other CELLS

Hello Forum,

I am new to the community and I look forward to hearing your expertise

I need some assistance with the best way to implement the below query which has two parts, I will provide the data I am trying to manipulate as I think that will help me explain:

Part 1

DATA (contained/Found in Sheet 2)

Animal Owner Cost
Dog Theirs 100
Dog Ours 150
Cat Theirs 75
Cat Ours 600
Dog Ours 280

The owner column effectively drives the +ve/-ve value of the cost column
Theirs = positive (+100) Ours = Negative (-150)

What is the best way to calculate a net amount for the Cost based on Animal? or return a net amount for each type of animal?

PART 2
On sheet 1, we will need to pull the net cost amount into the sheet based on the Animal, what is the most accurate way to achieve this?

I am an intermediate Excel user and I have a grasp of EXCEL formulae and some VBA

Thanks

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

Of course!! That's perfect. No data protection issues!! Make sure you include your expected results, too.

Hello Will,

Can you use this? Put in C14 and pull down.

=SUMIFS(Sheet1!$E:$E;Sheet1!$A:$A;A14;Sheet1!$D:$D;"Theirs")+((SUMIFS(Sheet1!$E:$E;Sheet1!$A:$A;A14;Sheet1!$D:$D;"Ours")*-1))