CountIFs results excluding duplicates

Hi and welcome to the forum

Maybe this

=A2&B2 in C2 and down

to get unique vales

in E2 and down.

=INDEX($C$2:$C$13,MATCH(0,INDEX(COUNTIF($E$1:E1,$C$2:$C$13),,),))

in D2 and down

=COUNTIF($C$2:$C$13,E2)

	A	B	C	D	E
1	Value 1	Value 2		Count
2	ABC12	bld	ABC12bld	3	ABC12bld
3	ABC13	bld	ABC13bld	1	ABC13bld
4	ABC14	bld	ABC14bld	1	ABC14bld
5	ABC15	bld	ABC15bld	1	ABC15bld
6	ABC12	bld	ABC12bld	2	ABC12ser
7	ABC12	bld	ABC12bld	2	ABC13ser
8	ABC12	ser	ABC12ser	1	ABC12urn
9	ABC12	ser	ABC12ser	1	ABC15urn
10	ABC13	ser	ABC13ser
11	ABC13	ser	ABC13ser
12	ABC12	urn	ABC12urn
13	ABC15	urn	ABC15urn

I don't think I understand you. Do you need the total of "ABC 12", the sum of (3+2+1)? Can you provide what exactly you would like to see by showing the final result and layout?

Please see attached file

hi there. i looked at your other post here & noted that you mentioned:

I looked at your post and tried to learn different formulas. It took me a while to understand Sixsenth formula. It seems too complicated & sophisticated for me. I like anything simple and easy, so I came up with a shorter & more mediocre one

so i'm a little confused. Alkey's solution seems to break it down for you. if you want something with less steps, it's going to be more complicated. try this array formula in row 2:
=SUM(IF(FREQUENCY(IF($A$2:$A$13=A2,MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1)>0,1))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

copy down. i don't know if you really need the numbers to repeat. you could create a unique list somewhere & just use the same formula, not referring to A2 but the unique list instead. the unique list can be done using Remove Duplicates or another array formula. you can check out the link in my Signature below on Tips & Tutorials I Compiled & download the file. it's in sheet 2

@benishiryo

Very nice approach to solve this puzzle! Thanks for helping out

Hmm...was trying to give you some Rep but the system wouldn't allow

@AlKey:
no worries about it. =)
you cannot give rep to the same person consecutively i think.

@Excel_learner:
by the way, i don't know if "a table of thousand hundred of samples" is referring to hundred of thousands? my formula will hang your computer if so. if that's really the case, you should consider PowerPivot since you are using Excel 2010. it's too huge a topic to share with, but the name alone should give you a headstart to start googling. The Pivot from there has a ready-made Distinct Counts for you.