count unique for each

mhillmann

Board Regular

Joined

Mar 30, 2015

Messages

154

• Mar 31, 2015

• #2

Here you go:

Code:

=COUNTIFS(B:B,"<>"&B1,A:A,A1) + 1

 

Upvote 0

D

deb

Active Member

Joined

Feb 1, 2003

Messages

400

• Mar 31, 2015

• #3

Perfect.

Thank you for sharing your expertise!!

 

Upvote 0

mhillmann

Board Regular

Joined

Mar 30, 2015

Messages

154

• Mar 31, 2015

• #4

Not a problem!

 

Upvote 0

D

deb

Active Member

Joined

Feb 1, 2003

Messages

400

• Mar 31, 2015

• #5

another question

What if I wanted to also check the ProjectNames on the NCC sheet. they are in column F

 

Upvote 0

mhillmann

Board Regular

Joined

Mar 30, 2015

Messages

154

• Mar 31, 2015

• #6

A	B	...	F	Result1	Result2
MD	McDan	...	Something	2	1
MD	McDan	...	Different	2	1
etc..

<tbody>
</tbody>

Are you saying the table is laid out like this and you want to also check if F is the same? It would be:

Result1:

Code:

=SUM(COUNTIFS(B:B,"<>"&B1,A:A,A1), COUNTIFS(F:F, "<>"&F1,A:A, A1)) + 1

Result2:

Code:

=COUNTIFS(B:B,"<>"&B1,A:A,A1,F:F, "<>"&F1) + 1

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 31, 2015

• #7

@deb;4116752

Row\Col	A​	B​	C​
1​	MD	McDan	2​
2​	MD	McDan	2​
3​	BW	Bill	2​
4​	AF	Alf	1​
5​	BW	Billie	2​
6​	MD	McZad	2​

C1, control+shift+enter, not just enter, and copy down:

Rich (BB code):

=SUM(IF(FREQUENCY(IF($A$1:$A$6=$A1,IF($B$1:$B$6<>"",
   MATCH($B$1:$B$6,$B$1:$B$6,0))),ROW($B$1:$B$6)-ROW($B$1)+1),1))

 

Upvote 0

D

deb

Active Member

Joined

Feb 1, 2003

Messages

400

• Mar 31, 2015

• #8

F is the column on sheet named NCC that also contains Project Names that I must count the uniques

Trying to do the 2 birds and one stone thing...

there are 2 sheets that contain the project Names. I want to count the unique Names on both sheets. The ProjectID is only on the first sheet.



.........A...........B..........C
1......MD.......McDan.....1
2......MD.......McDan.....1
3......BW.......Bill..........3
4......AF........Alf...........2
5......BW.......Billie........3

Sheet NCC
1......BW.......Bob.........3
2......AF........Alfred......2

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 31, 2015

• #9

Is MD a project and McDan a name? You talk about ProjectID but there is no header ProjectID, no headers at all for that matter.

 

Upvote 0

D

deb

Active Member

Joined

Feb 1, 2003

Messages

400

• Mar 31, 2015

• #10

Sorry
MD is ProjectID McDan is the ProjectName (Name)

 

Upvote 0