COUNTIF Unique values in adjaceme columns

[Paul Naylor]

Have a worksheet containing 2 columns.

Column A contains Order Numbers e.g. Ord123457 and Column B Names. Column A and B can contain duplicate values. Column C contains a list of all the unique names from column B

Formula on column D

Want a formula is column D , to lookup name in C against the name in B and count ONLY the unique order numbers column A registered against each of the names

ColumnA ColumnB ColumnC Column D
Ord12345 James James 2
Ord12345 James Bill 1
Ord23456 Bill Simon 3
Ord23456 James
Ord34567 Simon
Ord23456 James
Ord34567 Simon
Ord56789 Simon
Ord78912 Simon

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Paul Naylor]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Sorry , Excel 97

Found this , but doesn't seem to work in Excel 97. Also I have multiple worksheets ( Sun,Mon,Tue,Wed,Thur,Fri, Sat )with duplication is it possible to calculate across worksheets of varying number of lines i.e. ignoring blank lines?.

I don't really need it to calculate all the names master list column only selected ones say name in C3?

=IFERROR(ROWS(UNIQUE(FILTER(A2:A3000,B2:B3000=C3))),0)

 

[Fluff]

Not sure if this will work with 97

+Fluff v2.xlsm
	A	B	C	D
1
2	Ord12345	James	James	2
3	Ord12345	James	Bill	1
4	Ord23456	Bill	Simon	3
5	Ord23456	James
6	Ord34567	Simon
7	Ord23456	James
8	Ord34567	Simon
9	Ord56789	Simon
10	Ord78912	Simon
11
12
Main

Cell Formulas
Range	Formula
D2:D4	D2	=SUM(--(FREQUENCY(IF($B$2:$B$20=C2,MATCH($A$2:$A$20,$A$2:$A$20,0)),ROW($A$2:$A$20)-ROW($A$2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Paul Naylor]

Hi Fluff,

Works with up to date excel but getting a #Value error in 97 !!

What about some form of index /match function?

 

[Fluff]

Did you confirm it with Ctrl Shift Enter? If so it will be wrapped in {}

 

[Paul Naylor]

Did you confirm it with Ctrl Shift Enter? If so it will be wrapped in {}

Hi Fluff,

Apparently not as it works a treat thanks.
Is it possible to do this across Several worksheets (Sun,Mon,Tues,Wed,Thur,Fri,Sat) as if the order is not resolved on 1 day it will carry forward to other days ?

Here's the exact formula I've used:

=SUM(--(FREQUENCY(IF($B$2:$B$6796=J2,MATCH($A$2:$A$6796,$A$2:$A$6796,0)),ROW($A$2:$A$6796)-ROW($A$2)+1)>0))

 

[Fluff]

I think that you would need to create a separate formula for each sheet.

 

[Paul Naylor]

I think that you would need to create a separate formula for each sheet.

Hi Fluff ,

The only problem i can see with this that it will double count anything carried forward onto another sheet.

Only option I can think is to dump all the orders for a given person onto one sheet and then do the calculation on the 1 sheet.

Any ideas how to dump all the orders from each worksheet onto a separate worksheet?

 

[Fluff]

Not without knowing exactly what your data is currently like & how you want it to end.
But as that is a totally different question, you will need to start a new thread if you want help with it.