How To Count amount the same as one in excel?

[Heaven]

Dear All,


I want to know how to count the amount that the same in excel as 1,

For ex:
1
2
1
3
1
So these Number I want to count as 3, because it have 1 three time but I want to count it only one.

I am waiting for your help

 

[T. Valko]

Dear All,


I want to know how to count the amount that the same in excel as 1,

For ex:
1
2
1
3
1
So these Number I want to count as 3, because it have 1 three time but I want to count it only one.

I am waiting for your help

To count unique NUMBERS...

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5</TD></TR></TBODY></TABLE>


=SUM(--(FREQUENCY(A1:A10,A1:A10)>0))

Result = 5

Note that this will count the unique NUMBERS only. It will not work on text entries.

 

[Aladin Akyurek]

Thank you so much for your help.

You are welcome. Thanks for providing feedback.

 

[Heaven]

how to total amount by name?

Dear Mr. Excel,

I have one question, I want to sum income of my team by name without sort.

so please give me a guide.
Thank you in advance for your assist


Best Regards,

Dara

 

[Aladin Akyurek]

Re: how to total amount by name?

Dear Mr. Excel,

I have one question, I want to sum income of my team by name without sort.

so please give me a guide.
Thank you in advance for your assist


Best Regards,

Dara

Something like...

=SUMIF(TeamRange,Team,SumRange)

If you don't have a team range but just a range of players (PlayerRange)...

Create a range of players per team and name those ranges Team1, Team2, etc. and invoke:

=SUMPRODUCT(--ISNUMBER(MATCH(PlayerRange,INDIRECT(X2),0)),SumRange)

where X2 houses a string like Team1...

 

[T. Valko]

Re: how to total amount by name?

Dear Mr. Excel,

I have one question, I want to sum income of my team by name without sort.

so please give me a guide.
Thank you in advance for your assist


Best Regards,

Dara

Is this what you had in mind:

Book1
	A	B	C	D	E
1	Name	Amount	_	Name	Total
2	Name 1	66	_	Name 1	137
3	Name 3	39	_	Name 2	173
4	Name 1	37	_	Name 3	139
5	Name 3	100	_	_	_
6	Name 2	57	_	_	_
7	Name 2	71	_	_	_
8	Name 2	45	_	_	_
9	Name 1	31	_	_	_
10	Name 1	3	_	_	_
Sheet1

This formula entered in E2 and copied down:

=SUMIF(A$2:A$10,D2,B$2:B$10)