Sorry let me start again.

1. in the attached spreadsheet above you will see the formula does not seem to be adding up the units correctly, in some cells it is showing a zero instead of the sum of units.

2. secondly the codes I wrote were just examples of how I am trying to get it to work, if there is no duplicate unit counts than I need the formula to return the number of units shown.

3. However if there is a duplicate than I need the formula to return the sum of the units as it is doing now. please see below as an example of how I need it to work.

------------------sheet---Formula
------------------units----return
18175---SB-520384--1------ 0
18175---SB-520384--2------ 3
21222---SB-520384--1------ 1
40897---SB-520399--2------ 0
40897---SB-520399--1-------3
18173---SB-520384--1------ 1
18175---SB-520384--1------ 1

I hope I have made this all make sense.