List unique values and count cell values with different conditions

[nahaku]

Hello, I im trying to find formulas to help me make statistics, but I do not know how to get this. The formulas I found on internet helps always only with half of the problem.
I am trying to list unique value from [A] Yelow column for example GBL0004,GBS0083,...
then it could list GBL0004 divided by Region name from Column Blue column so for example GBL0004 A , GBS0083 A, GBS0083 C1, GBS0083 B1,GBS0088 A, GBS0088 B1 ....
it basically splits the person by location where they ware working.
and then just count with Brown Columns separetly. That is somethink i might do, but I am not able to count it with that condition so it would count for unique
IF (Picker [A]+ Region name ) = SUM(C:C) { or Sum D, E, F, G......}

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff.xlsm
	A	B								J	K
1	County
2	Buckinghamshire	A								Buckinghamshire	A
3	Buckinghamshire	A								Cheshire	A
4	Buckinghamshire	A								Cheshire	B
5	Buckinghamshire	A								County Durham	A
6	Cheshire	A								County Durham	C
7	Cheshire	B								County Durham	C1
8	Cheshire	A
9	County Durham	A
10	County Durham	C
11	County Durham	C1
12	County Durham	A
13	County Durham	C1
Data

Cell Formulas
Range	Formula
J2:K7	J2	=UNIQUE(A2:B13)
Dynamic array formulas.

 

[nahaku]

Hi & welcome to MrExcel.
How about

+Fluff.xlsm
	A	B								J	K
1	County
2	Buckinghamshire	A								Buckinghamshire	A
3	Buckinghamshire	A								Cheshire	A
4	Buckinghamshire	A								Cheshire	B
5	Buckinghamshire	A								County Durham	A
6	Cheshire	A								County Durham	C
7	Cheshire	B								County Durham	C1
8	Cheshire	A
9	County Durham	A
10	County Durham	C
11	County Durham	C1
12	County Durham	A
13	County Durham	C1
Data

Cell Formulas
Range	Formula
J2:K7	J2	=UNIQUE(A2:B13)
Dynamic array formulas.

yes that looks promising. Is it possible to take input from for example column A and column C ?in your example you take column A and B, what is easy but I need to take input from columns far from each other. because I have some data what are imported in to excel and there we are unable to change its format.

 

[Fluff]

You can do that like

+Fluff.xlsm
	A	B	C	D	E						K	L
1	County
2	Buckinghamshire	a	A	1							Buckinghamshire	A
3	Buckinghamshire	d	A	2							Cheshire	A
4	Buckinghamshire	c	A	3							CheShire	B
5	Buckinghamshire		A	4							County Durham	A
6	Cheshire		A	5							County Durham	C
7	CheShire		B	6							County Durham	C1
8	Cheshire		A	7
9	County Durham		A	8
10	County Durham		C	9
11	County Durham		C1	10
12	County Durham		A	11
13	County Durham		C1	12
Data

Cell Formulas
Range	Formula
K2:L7	K2	=UNIQUE(FILTER(A2:C13,(COLUMN(A2:C2)=1)+(COLUMN(A2:C2)=3)))
Dynamic array formulas.

 

[nahaku]

yes thank you, that is pretty much it :P

 

[nahaku]

I tried to paste it here, but it does not work, And I cant delete that post. Is possible to add to your formula =UNIQUE(FILTER(A2:C13,(COLUMN(A2:C2)=1)+(COLUMN(A2:C2)=3))) something like either filter what will take values from range of cells to ignore those name? or I could type in the formula what names to ignore? names are like GBS0007 ,GBX5567, TWXLOASJ ? I would like to get rid of those GBS0183 for example. I got those values by using your formula .

 

[Fluff]

How about
=UNIQUE(FILTER(FILTER(A2:C13,(COLUMN(A2:C2)=1)+(COLUMN(A2:C2)=3)),A2:A13<>"Cheshire"))

 

[nahaku]

Thank you for your help I wish I would know more stuff from Excel, it is very long time I work with Excel.
The formula is not working for me, there is some issue. when you look at the left site where i need that formula it says "SPILL" when i tried it on the right side with free space it showed only 1 entry and it added many empty cels too.
Anyway if This formula would work, I still could filter it only by 1 value, isnt it? Unfortunately I do not know how to use the "OR" function if it serves that purpose I could make formula to ignore one or the other...
If you want and can, is there something you could advice? No rush :P Thank you.
Screenshot: imgur.com/a/Pgt3XRp

 

[Fluff]

You have missed out one of the filter functions, compare your formula with the one in post#7
Also rather than posting images, please use the XL2BB add-in to post actual data to the thread

 

[nahaku]

I missed that one, Thank you, now it works.
=UNIQUE(FILTER(FILTER(Picking!B2:E201,(COLUMN(Picking!B2:E2)=2)+(COLUMN(Picking!B2:E2)=5)),Picking!B2:B201<>"GBS0183"))
one last thing, can i add more values "GBS0183" .... to ignore? Either if I type them or from some selected list. I have approximately 21 values what may occur but should not be counted for.