Help with Count unique and duplicate entries seperately.

[onp]

Hello,
I have been trying to count unique entries in two columns but haven't been able to get the desired results.
Help on this matter would be much appreciated

I have attached Sample sheet with Descriptions.
Hope it makes sense
Thank You

Cheers
Nino

[ChemistB]

Try this formula in D3

Please Login or Register  to view this content.

Note: "Orange " in cell A19 has a space after the formula causing it to miscount.

[crazyforexcel]

Hi,

Mr. ChemistB is this a viable option?

=IFERROR(INDEX($A$3:$A$29,MATCH(0,COUNTIF($D$2:D2,$A$3:$A$29),0)),"")

CTRL+SHIFT+ENTER to make it an array

[ChemistB]

Hey Eddie,
Your formula gives a unique list of the values in Column A (fruits). Nino was looking for a list based on two criteria (fruit and Ser#). It looks like he also wanted everything reported in the same row as its initial position.

[crazyforexcel]

Okay thanks I misunderstood the question.

[onp]

Hello Mr. Chemist
thank you for the quick reply,
The formula you have provided works Great And thank you For the "Note" on the space after the formula. I would have missed that completely

In the Count column E - is it possible to get results as shown in column H?
Basically I would need to count all same type of items without a serial number as a gran-total
and those of same type of items with a serial number as a single count in the corresponded cell

Example in the "Sample Sheet" column H:
Apple = 1 with serial number
Apple = 3 without serial number
Ultimately showing a total of 4


Currently I am getting the following result:
Apple = 4 with serial number
Apple = 4 without serial number
and that is not correct because it gives me a total of 8 apples rather than 4

Thank you
Cheers
Nino

[ChemistB]

In E3 copied down,

Please Login or Register  to view this content.