Sum unique text/number values given a criteria

[Spicey_888]

Hi All,

I have a problem similar to that of an earlier post today however that particular instance didn't help solve my problem.

I have a large array of data, and although countless attempts of using a combination of SUM, IF, FREQUENCY, COUNT etc I cannot get the desired outcome.

See attached the example worksheet!

In column C I wish to equate the total number of unique parts for that given provider. I know I could create a separate table and probably use a combination of count ifs but I have two main issues. Firstly the part ID's are not all numbers, some may be text only or a text-number combination or just a number and secondly I am using this column as a helper column for another function and thus need the answer calculated for each row of data even if the provider is the same, thus I'm thinking I will need an array formula with some cell locks applied. Further to this my database thus far is approximately 10,000 rows and growing and unfortunately may occasionally include Blank entries.

Is anybody able to give me some guidance on this? I have included a small example and manually entered the results I expect to see in Column C.

Count of unique entries.xlsx

Cheers,

Spicey

[cbatrody]

Hi,

See the attached file. I have added a helper column to identify the unique values and used sumif to get the totals.

In D2: =IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)>1,0,1)

In C2: =SUMIF(A$2:A$27,A2,D$2:D$27)

[Spicey_888]

Clever,

That's exactly what I needed.

Cheers